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!

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

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" wrote:

Hi GT,
What do the City and State parts of the cell look like? Are their formats
consistent?
Regards - 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.

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

"Dave" wrote:

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.

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!

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!

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!

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!

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!