Hi,
I have full address in once cell and I was just wondering if there any way to un-concatenate it in all different cells. Here is the example

Cell A1

16545 Dingo St
suite 656
Chicago, IL 54545

Please help me if you know how to save all of that cell into 5 different cells (address1, address2, city, state, address).

Thank you so much in advance

Hi sudkool,

Unless your cells have line feeds at the end of each line, this could be a very complicated task! Just consider:
1st Floor
123 St John St Ln
St Louis, IL 54545

If you do have line feedsat the end of each line, it'll be much easier and a macro solution will probably work best. If that's the
case, post your question in the microsoft.public.excel.programming NG

Cheers

--
macropod
[MVP - Microsoft Word]


"sudkool" wrote in message ...
|
| Hi,
| I have full address in once cell and I was just wondering if there any
| way to un-concatenate it in all different cells. Here is the example
|
| Cell A1
|
| 16545 Dingo St
| suite 656
| Chicago, IL 54545
|
| Please help me if you know how to save all of that cell into 5
| different cells (address1, address2, city, state, address).
|
| Thank you so much in advance
|
|
|
|
| --
| sudkool

hi sudkool:

If the address is in A1 assuming that there are no extra characters on the
end:

[A3] line 1=LEFT($A$1,FIND(CHAR(10),$A$1,1)-1)
[A4] line 2=MID($A$1,LEN(A3)+2, FIND(CHAR(10),$A$1,LEN(A3)+2)-1-LEN(A3))
[A5] town=MID(A1,LEN(A3)+LEN(A4)+2,LEN(A1)-LEN(A3)-LEN(A4)-11)
[A6] state=MID(A1,LEN(A1)-7,2)
[A7] zip=RIGHT(A1,5)

You could use formula to find the places of the , in the last line but I was
being lazy.

You can have a try if you like.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"sudkool" wrote:


Hi,
I have full address in once cell and I was just wondering if there any
way to un-concatenate it in all different cells. Here is the example

Cell A1

16545 Dingo St
suite 656
Chicago, IL 54545

Please help me if you know how to save all of that cell into 5
different cells (address1, address2, city, state, address).

Thank you so much in advance




--
sudkool

On Fri, 9 Feb 2007 03:32:05 +0000, sudkool
wrote:


Hi,
I have full address in once cell and I was just wondering if there any
way to un-concatenate it in all different cells. Here is the example

Cell A1

16545 Dingo St
suite 656
Chicago, IL 54545

Please help me if you know how to save all of that cell into 5
different cells (address1, address2, city, state, address).

Thank you so much in advance

Assuming the format is somewhat similar to what you have posted, you could use
Regular Expressions.

The formulas assume that address2 will be a blank line if not present.
The formulas also assume that the STATE is always characterized by the sequence
<comma<space<capltr<capltr<space

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

It is easily distributable with the workbook, if that is an issue.

Then use these formulas:

address1: =REGEX.MID($A1,".*")
address2: =REGEX.MID($A1,".*",2)
city: =REGEX.MID($A1,".*(?=,\s[A-Z]{2}\s)")
state: =REGEX.MID($A1,"(?<=,\s)[A-Z]{2}(?=\s)")
zip: =REGEX.MID($A1,"(?<=,\s[A-Z]{2}\s).*")


--ron