Leading zeros in zipcode

[cmcreynolds]

Hello

I received a CSV file of addresses and the zipcode field is giving me fits with leading zeros (from the Northeast US in particular).

There are several versions of the zipcode within the field: if it's zip+4, then the leading zero is in there; if it's just the zip, the leading zero is missing. I don't REALLY need the +4 part at this point.

This is what I did but it did not work:

• Converted to an Excel table
• Converted the field "Special > Zip" (Yay! It added the leading zero to the records without the +4 but...)
• Used "Data > Text to Columns" and got rid of the +4 parts
• Everything is fine UNTIL I convert the table back to a range. Poof, the leading zero is gone in the records that didn't have the +4 originally
• (I've also tried copying the column and pasting using the various options - (Paste, Values, and Formulas) and each made the leading zero vanish.

Am I missing how to copy "AS" text format or something that keeps that nasty little leading zero?

I know I've seen this before but searching the MRExcel forum didn't return anything regarding zipcodes. I apologize for any repeated post.

Any suggestion would help.

Thank you!

 

[shg]

You can use a formula in a convenient column:

=text(a1, "00000")

Then copy that column and paste as values over the original.