cell data entry formatting

[theYaniac]

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.

 

[louisH]

Hi,

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" :

0000000000

Hope it helps.

 

[theYaniac]

Thank you, I feel silly for not knowing this.

 

[sergieveze]

Hi.
a basic macro can do it.

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

Just add this into a module in VBA.
Then use it as a formula in excel cell like this :
=addZero(A1)