Leading Zeros

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Jan 22, 2004

• #2

Format the target cells as Text before entering such account numbers.

 

Upvote 0

B

BuddieB

Board Regular

Joined

Aug 6, 2003

Messages

174

• Jan 22, 2004

• #3

Another option is to format the cell with a custom number format:

i.e. 00000000 if there are 8 digits in the account number,

though I am sure there are added benefits to formatting the cell as text that Aladin would be better able to enlighten us about.

 

Upvote 0

Todd Bardoni

Well-known Member

Joined

Aug 29, 2002

Messages

3,042

• Jan 22, 2004

• #4

I have always preferred BuddieB's approach...but I do wonder why Aladin said to format as text.

 

Upvote 0

C

ChrisTolo

New Member

Joined

Dec 5, 2003

Messages

21

• Jan 22, 2004

• #5

I deal with ISBN numbers a lot and I have the same problem. I use the following code alot to fix this problem.

Code:

If Mid(C.Value, 1, 8) Like "########" Then
        C.NumberFormat = "@"
    Select Case Len(C.Value)
        Case 8
                C.Value = "00" & C.Value
        Case 9
                C.Value = "0" & C.Value
    End Select
End If

ISBN numbers are always 10 digits. This code formats the cell C as text and "fixs" the number by adding the missing zeros. This will not work if you are not using VB or if the number of digits is not fixed. Otherwise, you just have to be very careful to set the format to text before you put the data in.

If anyone knows a way to turn this function off in excel I would love to know.

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Jan 22, 2004

• #6

BuddieB said:

Another option is to format the cell with a custom number format:

i.e. 00000000 if there are 8 digits in the account number,

though I am sure there are added benefits to formatting the cell as text that Aladin would be better able to enlighten us about.

Click to expand...

Not a great mystery. When you do e.g., a Vlookup, you might run into trouble, in particular when you download the related data from other systems.

 

Upvote 0

B

BuddieB

Board Regular

Joined

Aug 6, 2003

Messages

174

• Jan 22, 2004

• #7

I knew there had to be a good reason behind it, thanks for the tip.

 

Upvote 0

J

jcm996

New Member

Joined

Sep 6, 2003

Messages

23

• Jan 23, 2004

• #8

Thanks for the help and the warning on vlookup, but that really won't be used anyway. Basically we use excel to allow our customers to recieve large number of account from their clients, then we just put special column headers on, save as tab delimited and our software will load all the data for them. Saving hours of data entry time.

Thanks again

 

Upvote 0