I had to try this and I am getting the info for the 1st address in column b
repeating every other row in f-h.
The second and third address is not being entered into columns f-g.
cil
"Dav" wrote in message
...

From the original data I would try something like the attached and if it
works then split the last line of the address

Insert a column to the left
in A1 put =IF(B1=UPPER(B1),1,0)
in A2 put =IF(ISERROR(FIND(B2,UPPER(B2))),A1,A1+1) and copy it down to
the end of your data

In a free column my example column E put a 1 in E1, 2 in E2 and copy
down so you get sequential numbers
in F1 put =VLOOKUP(E1,$A$1:$B$4000,2,0)

in G1 put
=IF(OFFSET($A$1,MATCH(E1,$A$1:$A$400,0),0)=OFFSET( $A$1,MATCH(E1,$A$1:$A$400,0)+1,0),OFFSET($B$1,MATC H(E1,$A$1:$A$400,0),0),"")

in H1 put
=IF(G1="",OFFSET($B$1,MATCH(E1,$A$1:$A$400,0),0),O FFSET($B$1,MATCH(E1,$A$1:$A$400,0)+1,0))

these can then be copied down for as many rows as you need

If they work I would copy them as values and delete the formula

then use the left, right mid functions with search and len and find to
split the data in column H into the appropraite Fields

Regards

Dav


+-------------------------------------------------------------------+
|Filename: Split Addresses.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5048 |
+-------------------------------------------------------------------+

--
Dav
------------------------------------------------------------------------
Dav's Profile:
http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=562319