Ron-
Thanks for this information.
An example of this is Jane W Doe 1234 Anyplace Street Minneapolis MN
55443-2913

I know that I can pull zip and state over because as you mentioned they are
same number of spaces. Would there be anyway to pull the city name or
address?
Thanks!

"Ron Rosenfeld" wrote:

On Tue, 7 Apr 2009 19:20:01 -0700, anna f
wrote:

Hello!

I have thousands of entries in Excel where there is a First and Last Name,
followed by a street address, City State and Zip all in one cell. I'd like to
do a mail merge mailing, therefore I need to seperate the data. Does anyone
know how I can accomplish all of these tasks? Or at least part of these
tasks, so I don't have to manually extract the data.

- The address usually begins with a number (Not those in the cases of a P.O
Box)
- There isn't a comma or any other delimiter between City, State or Zip.

Your help is greatly appreciated!! Many thanks!

Since you don't post any examples of the specific formats of these strings, the
advice can only be general.

But you would have to determine how you can unambiguously separate the
different parameters.


Using only the information you have posted:

The zipcode is probably always the last entry, and could be five digits, nine
digits, or 5-4 digits.

The state probably precedes the zip code and is either a two letter
abbreviation or a one or two word string.

The city precedes the state and, if there is no separator between it and the
address, could possibly be determined by doing a lookup on the zip code (web
query). If there is a comma, that could be used.

The first name is probably the first word in the string.

Depending on the separators, it may or may not be possible to separate out the
last name from the street address.


If you post some real examples, you might obtain more specific information.
--ron