Adding a 0 to a four digit number

[jermsalerms]

I have 4000+ zipcodes in a .csv file that I have imported to excel.

The .csv file shows all zipcodes that start with 0 as a for digit
number instead of 5. I need to add the 0 infront of the four that show
up.

for example

19601 fills in correctly
08054 shows as 8054

I need an if then statement that basically says if 4 digits then add a
0 infront is 5 digits remain the same.

 

[Pete]

As these are codes then they should be stored as text, so the following
will do what you want:

=IF(LEN(A1)=5,""&A1,IF(LEN(A1)=4,"0"&A1,"00"&A1))

This caters for 3-, 4- and 5-digit numbers, but hopefully you can see
the logic of how to cope if you also have 2 digit numbers. It does not
check for 6-digit or larger numbers.

Hope this helps.

Pete

 

[Ron Rosenfeld]

I have 4000+ zipcodes in a .csv file that I have imported to excel.

The .csv file shows all zipcodes that start with 0 as a for digit
number instead of 5. I need to add the 0 infront of the four that show
up.

for example

19601 fills in correctly
08054 shows as 8054

I need an if then statement that basically says if 4 digits then add a
0 infront is 5 digits remain the same.

When you say the .csv file shows the numbers as four digits, are you looking at
the .csv file with a text editor, or merely opening it in Excel?

If, when you open it in a text editor (e.g. Notepad), the zipcodes display
properly, then you can change the file type to a .txt file. When you open it
in Excel you will go to the text editor and can specify the column type as
"Text".

If that is not possible, what to do depends on your goals.

If you are just interested in having the column appear with the leading zero,
Select the column
Format/Cells/Number/Custom Type: 00000

If you need to change the zip codes to text strings, then, assuming the
zipcodes are in column A, in some unused column enter the formula:

=TEXT(A1,"00000")

Copy/drag down as far as needed.
Edit/Copy
Select A1 (or whatever the initial cell is).

Edit/Paste Special/Values


--ron

 

[mphell0]

Format Cells>Special>Zip Code

 

[jermsalerms]

I ended up using petes suggestions with and extra if statement that said
that if the cell is less than 2 numbers the result is "". This prevented
me from having 0's show up when there was no zipcode provided.

I was trying to write the formula along these lines and could remember
that is was the & symbol that I was needing to make it all come
together.

Thanks

 

[Pete]

Thanks for the feedback.

Pete