cell data entry formatting


New Member
I am trying to format the cells in which I enter data. I receive invoice numbers from our department head, the invoice numbers vary in length. I need all entries to be 11 digits in length. If the invoice number I receive is only six digits in length, I need the first five to be zeros. Example, I receive the invoice numbered (123456) I need it to display as (00000123456) when I enter it into the system. Same for an eight digit invoice (12345678) needs to be (00012345678) when entered. Could someone suggest a way to format the cells where I am doing the data entry to display as needed? Thank you in advance.


Board Regular

If you just want to display it this way, it can be done very easily using Excel number formats.
just create a new personalized format with 11 "0" :


Hope it helps.


New Member
a basic macro can do it.

Function addZero(cell As Range)
c = cell.Cells(1, 1)
While (Len(c) < 11)
c = "0" & c
addZero = c
End Function

Just add this into a module in VBA.
Then use it as a formula in excel cell like this :

Some videos you may like

This Week's Hot Topics