Prevent Excel from removing preceding zeros when importing text

**NathanC** · 09-11-2012, 01:09 PM

Hi!

I'm importing a list of numbers, viz.:

1034 2788 0612 1009 0002 1234 etc.

I go to "Data" tab, chose "From text," and procede to import. I want one number in each cell, and the preceding zeros are essential.

I've noticed that when in "General" or "Number" Format, Excel will always remove those preceding zeros when typed directly in. Only in "Text" format will it leave them there.

However, when importing data, I select the import format as "Text," and it still removes my zeros.

Any solution?

**handerson0603** · 09-11-2012, 01:15 PM

Import them as numbers, but format the column as "0000" (under Custom format).

**shg** · 09-11-2012, 01:18 PM

If I open a text file containing those values and select Text for all the fields, they import as text with leading zeros intact. How are you doing it?

**NathanC** · 09-11-2012, 01:25 PM

Anderson--Perfect, just what I needed! I hadn't ever used Custom before...this post probably should have gone under "Basics."

And congratulations on your state...it's a wonderful place!

**NathanC** · 09-11-2012, 01:27 PM

shg--Another Texan! It's a reunion!

I select From Text, Delimited, Tabs (the separators in my case), check Treat consecutive as one, select Text, and Finish. Maybe you have 2010?

**shg** · 09-11-2012, 01:50 PM

You need to select Text for all of the fields.

Handerson's solution may work, but the underlying values are still simple numbers. It depends on how you use the results downstream: If you catenate two of them, for example, the leading zeros will disappear; if you test the length of one, the leading zeros will be ignored.

**handerson0603** · 09-11-2012, 02:56 PM

Nathan,

I have no problem importing text with leading zeroes in 2007, and I don't have to select Text for all fields. Not sure why it is not working for you.

I got used to using the custom format because I still needed to use the cell contents as numbers for doing various mathematical operations, but I wanted the leading zeroes displayed. SHG is right, though, if you need to use them as text in any other operations, you will have issues. TEXT(A1,"0000") will convert the number back to 4-digit text. Likewise, VALUE(A1) will convert text to a number (use the custom format to display leading zeroes).

If you found this helpful, please click the star at the bottom left of this post.

**NathanC** · 09-11-2012, 04:00 PM

Thanks, that is helpful. At this point it's not making much of a difference, since all I needed was to extract a sample from the population, but I'll remember it for the future.

And hey, I may be a noob, but I do know about the star--I had already voted your previous post helpful

Thanks again!