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&"012345 6789"))+4)

Rick


"GoodTrouble" wrote in message
...
Thanks! Works great!

"Rick Rothstein (MVP - VB)" wrote:

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&"01234 56789"))+4)

Rick


"GoodTrouble" wrote in message
...
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)" wrote:

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&"0123456 789")),5)

Rick


"GoodTrouble" wrote in message
...
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!