separating address elements in one cell

[peggyo]

I have a large list of names and addresses I need to separate int
columns. Each cell includes one name & address, including first (mayb
a middle name) last name, address, city, state and zip. The data i
separated by commas but isn't always consistent. I am trying to figur
out a way to split the data into it's own columns (i.e., first name
last name, address 1, address2, address3, city, state, zip).

Here is some sample data so you can see some of the issues I am dealin
with (sometimes there is more than one address line or a first middl
and last name, for example). Any suggestions are so appreciated
thanks!

mike shoafstall, Colgate University, Po Box M4026, 13 Oak Dr, Hamilton
NY 13346-1338
Mary Van Stedt, 2200 S Beaumont Ave, Kansasville, WI 53139-9725
Jennifer Harme, 2904 E 21St St, Sioux Falls, SD 57103-3308
Karen L Jordan, 71 Dreamland Park Dr Apt 4, Fleetwood, PA 19522
Kristen Rossi, Medical Center Dev., Alumni Relations, 3535 Market S
Ste 750, Philadelphia, PA 19104-330

 

[Aladin Akyurek]

Data|Text to Columns, check Comma as delimiter.

 

[Jason Morin]

Select the column and go to Data > Text to Columns and
use "," as your delimiter.

HTH
Jason
Atlanta, GA

 

[peggyo]

Two problems I am having with using text to columns. Once I do that:
1. The first & last name are in the same field but I need the
separated. Sometimes there is a middle name or a two-part last name:
Mike Shoafstall
Mary Van Stedt
Jennifer Harme
Karen L Jordan

2. In the original file, some addresses have one address line (on
comma), others have up to three. So, the column contents aren'
consistent (city might be in the 3rd column, 4th column or 5t
column).

Anyone have suggestions on these two items

 

[David McRitchie]

Hi Peggy,
For the first problem make a global change to your column
change all " van " to " van|"
if you don't want van in lowercase do this as case-sensitive once for
"Van" and once for "van" to retain what you have (ctrl+H).
You can then use SepLastTerm macro on my page
http://www.mvps.org/dmcritchie/excel/join.htm#septlastterm

As for the second problem
http://google.com/groups?selm=#[email protected]
--

 

[T Hamann]

Peggy,
There are a handlful of data tools available for parsing
both the Name elements and address elements even if they
do not have a consistent delimiter. recommendations 1) Do
a web search for parsing software (PeolpeSmith or other)
2) Contact a local mail house (these people deal with all
sorts of formatting issues and could either fix it for you
or offer recommendations. 3) If you want to outsourse it I
would glad to give you a quote.

Hope this helps.
Todd Hamann
585-259-0061
Rochester NY

 

[David McRitchie]

Use Text to Columns under Data menu, then choose delimited
and on the next dialog use comma for the delimiter. See what you
get. It's not too difficult the realign the columns

If zip state codes appear in more than one column and zip codes
appear in more than one column so they don't line up then take a
look at this thread.
http://google.com/groups?selm=#[email protected]

expecting something like
firstname, lastname, address1, address2, address3, city / town, zipst, zip+4
but some may appear as
firstname, lastname, address1, city, zipst, zip+4

Page on Mail Merge to print labels is
http://www.mvps.org/dmcritchie/excel/mailmerg.htm
also of interest
Snaking columns
http://www.mvps.org/dmcritchie/excel/excel.htm
Reformatting data
http://www.mvps.org/dmcritchie/excel/join.htm
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm