separate address data in a cell

[Guest]

Greetings, I have this information in one cell-
777 Anywhere Street
Happy Town, CT
The only comma is just after town. I need to separate out into street
addresss, town and state. How do I do this?

Thanks

 

[Guest]

Try this:

The carriage return could be one of the followin ASCI codes:
0010
0013
0009

Select your column of cells
Edit|Replace
Find what: (Hold down the [Alt] key...type 0010...Release the [Alt] key
Replace with: , (that's a "comma")
Click the [Replace All] button
(if it can't find any...sequentially try the others until you get "hits")

Data|Text-to-Columns
Delimited
Delimter: Comma
Click the [OK] button

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Ron Rosenfeld]

On Sat, 18 Mar 2006 14:44:28 -0800, Joe in CT <Joe in

Greetings, I have this information in one cell-
777 Anywhere Street
Happy Town, CT
The only comma is just after town. I need to separate out into street
addresss, town and state. How do I do this?

Thanks

Download and install Longre's free morefunc.xll add-in from

Assuming the street address is on the first line; the only comma is between the
city and the state; and the state is always a two capital letter string at the
end:

Street Address:
=REGEX.MID(A1,".*")

City
=REGEX.MID(A1,".*(?=,)")

State
=REGEX.MID(A1,"\b[A-Z]{2}$")
--ron