Leading Zeros

[jcm996]

Is there anyway to make excel leave leading zeros? I have a client who has account number like 0147364 and when trying to manipulate data in excel it removes the leading zero leaving 147364. Can this be stopped?

Thanks

 

[Aladin Akyurek]

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

 

[BuddieB]

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.

 

[Todd Bardoni]

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

 

[ChrisTolo]

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

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.

 

[Aladin Akyurek]

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.

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.

 

[BuddieB]

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

 

[jcm996]

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