Sleeper: Help with Zip Codes

[AbnRanger]

This is my first try at using this site, so thanks to all and my aplogies if I don't get this posted properly.


I'm sure there is a simple solution to my problem, but my VBA skills are poor.

I have quite a bit of data in Excel that includes US zip codes in one column. Most of these zip codes are in the 5+4 format (#####-####). Some is in the 5 digit format only. Some is incorrect. Some is a foreign zip code equivalent.

What I need is to convert the 5+4 zip codes into the 5 digit format, while leave the 5 digit, errors, and foreign codes alone.

Reformatting the column using the zip code format available seems to have no effect, so it seems to me I need a macro. On the other hand, if there is a way to force the format to work, that might be the simplest approach.

Thanks for your help!

[Jacob Hilderbrand]

Do you just want the first 5 numbers in the cell?

If your data starts in A1 then put this in an unused column.


=Left(A1,5)

Then fill down.

[hairywhiterabbit]

DRJ's formula will indeed work if your foreign numbers, and errors are 5 digits or less. If you have codes longer than this (eg British postcodes) then the following may be better, applied as DRJ described:


=IF(LEN(A1)=10,LEFT(A1,5),A1)

If you still have 10 digit numbers you want to keep, then you could check for the dash.


=IF((LEN(A2)=10)*(MID(A2,6,1)="-"),LEFT(A2,5),A2)

Giddy-up!

Cheers,
Andrew

[Bob Phillips]

Do you just want the first 5 numbers in the cell?
=Left(A1,5)


May not be a good idae as it may destroy some foreign codes, e.g. UK BH15 1XY would end up as BH15 .

Question to the OP. You say what you want done with the 5+4 US zip codes, but what do you want done with the errors and foreign codes. And is the #####-#### actual data, or is the - there simply as a result of the formatting?

[AbnRanger]

I appreciate the suggestions. Using them and then scrolling through and manually fixing the non-US postal codes that weren't handled properly worked out. I realize the manual part was not very elegant, but I used conditional formatting to highlight the non-US bearing cells and then inspected them.

To answer the last question, the hyphen was typed in by users who filled out a web page survey, it did not come from imposed formatting (mask).

Thanks for the help -- I'm doing this pro bono to help a non-profit organization.

[Bob Phillips]

So is this solved now or is there still work to do?