cell data entry formatting

theYaniac

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.
 

louisH

Board Regular
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.
 

sergieveze

New Member
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)
 

Some videos you may like

This Week's Hot Topics

Top