help parsing an address

[benzguy123]

i have a ss with 12000 addresses in one cell. that is each row is
different person, but their address is in one cell. i am trying t
parse it so that the name is in one cell, line1 address in the next
line2 in the next, city in the next, st in the next, zip in the next.

examples
"First Wisconsin Center
777 E. Wisconsin Avenue
Milwaukee, WI 53202-5367 U.S.A."

"880 West Herritage Park Blvd
Suite 900
Layton, UT 84041 U.S.A."

bad news, some only have one line (bad address),
some have only one address line some have 2, some have zip + 4 som
don't. i'm stumped...attached is a small sampl

Attachment filename: sample2.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=63341

 

[Bernie Deitrick]

BenzGuy,

Since your strings have returns in them, you can use Data | Text to
columns.. Delimited, use other, then in the special character box hold down
the alt key and type 0010 using your numeric keypad. That will split the
cells based on the return.

For those cells that lack a second address, after you do the text to
columns, sort based on the last column of data. All the cells that are
blank should group together. Select the city/state/zip cells from those
records and drag them over one column to match the other records.

HTH,
Bernie
MS Excel MVP

 

[benzguy123]

problem is there are over 10,000 addresses, i was looking for a simpl
macro or vb script if anyone was good enuf. i'm not...

 

[AlfD]

Hi!

1. Your sample was not varied enough to get a feel for the sort o
pattern (or lack of it) involved.

2. I would think that the work involved in trying to find pattern i
order to "program" a solution might well exceed the work involved i
following Bernie Deitrick's advice. Some judicious sorting along th
way can show up areas of pattern. Search and replace can enable furthe
text to columns steps and so on. Worth a try. The number of record
isn't the issue: it's the variety, and there won't be 12,000 differen
ones!

Al