Need to format varying zip codes...

[GoodTrouble]

I have an exported Spreadsheet which contains customer information which will
in turn be used for a mail merge...

However, because there is no validation on the input field in the program
doing the exporting the Zip Codes on these addresses is not formatted
correctly. To further complicate the problem the program exports the city
state and zip all in one field.

I need a way to find the Zip Code portion of the cell and format it to only
the typical 5 digit format (Not the longer # digit format...) For example:

The data is as follows:

A2 = Business Name B2 = Customer Name C2 = Address D2 = CityStateZip

The Zips will either be something like 12345 or 123456789 and thus I need to
get the 9 character zip down to the first 5 (keep in mind it is contained
with the City and State as well in the same cell...)

Thanks so much for your help in advance!

 

[Dave]

Hi GT,
What do the City and State parts of the cell look like? Are their formats
consistent?
Regards - Dave.

 

[GoodTrouble]

Dave,

The city and state zip will always be in the "City, IN 12345"
format...however the city will not always be the same and could vary in
characters...

Thanks for your time,
Tom

 

[Dave]

Hi, sorry to be pedantic. Does it really have a comma and a space after the
city?
Please post a sample of a few CityStateZip cells.
Regards - Dave.

 

[GoodTrouble]

Dave,

No problem at all, I don't have access to the spreadsheets as I am at home,
but I badly need an answer to this...They do appear exactly as I said
before...For instance...

D2 may contain Indianapolis, IN 46207
D3 may contain Dallas, TX 75204-1545
D4 may contain Rockford, IL 611071735

So, as you can see, the city will contain varying numbers of characters, and
so will the zip code...the zip code may also contain the "-", but most of the
time will not.

Is there a way for Excel to look for the first number in the cell, count 5
numbers, then truncate the rest? Seems like the only logical way for
me...Anything you can provide will be greatly appreciated!

Thanks again!
Tom

 

[Rick Rothstein \(MVP - VB\)]

Assuming your data starts on Row 2 and the CityStateZip data in in Column D,
then try this (copy down as needed)...

=MID(D2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},D2&"0123456789")),5)

Rick

 

[GoodTrouble]

Rick,

This will work for isolating the Zip code, but erases the city and state as
well...I just need to trim down the zip code IF there are more than 5 digits
in it...

 

[Rick Rothstein \(MVP - VB\)]

Sorry, I did not get that you wanted to retain the city and state from your
initial posting. Try this formula instead...

=MID(D2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},D2&"0123456789"))+4)

Rick

 

[GoodTrouble]

Thanks! Works great!

Sorry, I did not get that you wanted to retain the city and state from your
initial posting. Try this formula instead...

=MID(D2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},D2&"0123456789"))+4)

Rick

 

[Rick Rothstein \(MVP - VB\)]

You are welcome. In looking at it again, you can use an ever so slightly
better formula to do the same thing...

=LEFT(D2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},D2&"0123456789"))+4)

Rick