This would be a perfect job for an intern. If I had one.

January 24, 2008 7:47 AM

MS Excel filter: I'm editing a huge spreadsheet and Excel doesn't like my zip codes. How can I quickly fix this? more after jump...

I'm doing a mass mailing and I exported about 9000 names from our customer list in Filemaker, and Excel deleted the zero at the beginning of the East coast zip codes. (i.e. Worcester, MA 1602 instead of 01602) There are about 500 zip codes like this and I have to believe there's a quick way to make a universal change and stick in the missing zero at the beginning of the zip codes. I already formatted the cells to recognize nunbers as text, but that still leaves me to fix each one by hand.

Any ideas on what to do? And please explain it in simple steps because I'm not as technologically inclined as some.

posted by LiveToEat to computers & internet (9 answers total)

The zero isn't gone - Excel hides zeros before decimal points in cells it thinks are numbers.

To fix this, simply format the column as text before you paste in your zip codes.
posted by Happy Dave at 7:54 AM on January 24, 2008

Format the cells as type Special: Zip Code and it will keep the leading zeroes.

To be specific: in the menu, Format > Cells..., select Special as the category and Zip Code as the Type.

(I'm a bit surprised that Excel doesn't call it ZIP code, which is the proper name.)
posted by smackfu at 7:54 AM on January 24, 2008

You could solve this with a formula

=IF(len(A1)=4,"0" & A1,A1)

Where A1 is the zip code field.

FYI, len counts the number of characters in the field.
posted by Pants! at 7:56 AM on January 24, 2008 [1 favorite]

First, Excel also has a format for zip codes under the "special" category in formats. Try formatting the column as a zip code and see if that reinstates the leading zero.

If not, format the column (A) as text, format the column next to it (B) as text (or zip code) and paste this into (B): =IF(LEN(A1)=4,CONCATENATE(0,A1),A1)

(Tweak the A to be whatever column your failed zip codes are in.)

That should, in theory, work.

On preview, what they said.
posted by fuzzbean at 7:57 AM on January 24, 2008

If you had a similar problem (it used to come up for me with ISBNs) you could use Format: Number: Custom

then in the type box enter 0####
posted by drezdn at 8:01 AM on January 24, 2008

Thanks everyone. Specifically, smackfu. You saved me valuable time that I can now spend reading metafilter here at work.
posted by LiveToEat at 8:05 AM on January 24, 2008

Shouldn't these be treated as strings, even if you didn't have a zip-code-rendering type? Sure, they /look/ like numbers, but that doesn't mean you should enter them that way.
posted by cmiller at 8:40 AM on January 24, 2008

Watch out for ZIPs in Puerto Rico, which may look like 00123 (two leading zeros).
posted by DandyRandy at 2:52 PM on January 24, 2008

Just so you know, the values are not being stored with the leading zeros, they're only being displayed with leading zeros. So if you had to use the values in a formula, it should be =IF(A1=1602,... not =IF(A1="01602",...
posted by junesix at 4:18 PM on January 24, 2008