[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Excel Leading Zeros, store them, not just format them

Posted on 2009-12-30
Medium Priority
Last Modified: 2012-05-08

Several times each week, we get a file containing thousands of zip codes, stored as numbers (without leading zeros), but formatted with 00000 to get the leading zero's as appropriate.  

If I click in the cell fore one of the zip codes, of course, there is no leading zero, just the number. I really want the cell to be text with the leading zero.

What's the quickest/simplest way to convert the entire column of numbers (formatted with leading zeros) into text, preserving the leading zero's without using apostrophy???

I know I could insert a temporary column, and use function:  =TEXT(B3,"00000"), drag it down, insert another column, copy, paste special values, but there's going to be a lot of columns like this, and I get two files a week.

I'd like to avoid using macros or anything complicated because the person who will be doing it going forward is not very good in excel.

I've tried a variety of crazy things, even saving the .xls to CSV, and trying to import it back, but alas, it keeps wanting to store as number.

I was hoping there would be some wizard base method like text to columns but I can't seem to make it do what I want.

Any ideas would be greatly appreciated!


Question by:mike2401
LVL 59

Assisted Solution

by:Saurabh Singh Teotia
Saurabh Singh Teotia earned 200 total points
ID: 26147233
You already have listed all the ways that you can use to do what you are looking for, The only other way that i can tell you to convert is by using macros as that will be 1 step process.
Let me know if you need that and will write code for you, Otherwise you cant do what you are looking for without inserting an extra column in the file.
LVL 18

Expert Comment

ID: 26151449

A VBA approach..

Adjust the pattern in code.

Select the range and run the code.

Sub kTest()
Const ZipPattern As String = "00000-0000"
With Selection
    .NumberFormat = "@"
    .Value = Evaluate("=text(" & .Address & ",""" & ZipPattern & """)")
End With
End Sub

Open in new window

LVL 18

Accepted Solution

krishnakrkc earned 1400 total points
ID: 26151465

Correction in code


.Value = Evaluate("=text(" & .Address & ",""" & ZipPattern & """)")


.Value = Evaluate("=if(" & .Address & "<>"""",text(" & .Address & ",""" & ZipPattern & """),"""")")

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 13

Assisted Solution

by:Jeff Darling
Jeff Darling earned 400 total points
ID: 26158808
This problem happens because the data wasn't imported or entered into excel correctly.

If importing from a CSV file, try renaming the file to .TXT so that Excel will invoke the text import wizard.  Then, set the zip code field as TEXT.  This will keep the leading zeros.

Excel thinks that its doing you a favor with its automatic conversions.  Select TEXT as the type for any fields that you don't want converted, such as zip codes, phone numbers etcetera.


Author Comment

ID: 26180513
Originally, I said I didn't want to use a macro (mainly because I thought it would be a pain to deploy using a personal.xls that would auto-load each time excel would launch.

I learned at:

how to use an .XLA (excel add-in), something I never knew before.

I modified Kris's macro for 5 digits with leading zeros:

Sub digit5LeadingZeros()
Const ZipPattern As String = "00000"
With Selection
    .NumberFormat = "@"
 .Value = Evaluate("=if(" & .Address & "<>"""",text(" & .Address & ",""" & ZipPattern & """),"""")")
 End With

End Sub

In tools |  add-ins, I have checked off the name of the xla (which contains the above macro).

The only odd part is if I click on Tools | macros |  macros, the above macro isn't listed (whether I pick macros in "book2, this workbook, or all open workbooks".  However, if I tools | macros | visual basic editor, I see two hives (one form the current workbook, and one for the vba project associated with the xla file containing the above macro.  If I drill down modules | module1 and click on the macro, I can play it and sure enough it changes the selected cells.

What's an easy way for the end-user to select the macro that's been added via an add-in so it's always available when excel runs?


Author Comment

ID: 26276083
Thanks everyone.  I'm going to close this call with great appreciation.  I'll open a separate call about being able to "play" macros incorporated from the add-ins feature.

Author Closing Comment

ID: 31671284

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question