Reply to thread

Message

[QUOTE="Dav, post: 3652637"]
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,MATCH(E1,$A$1:$A$400,0),0),"")

in H1 put
=IF(G1="",OFFSET($B$1,MATCH(E1,$A$1:$A$400,0),0),OFFSET($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: [URL]http://www.excelforum.com/attachment.php?postid=5048[/URL]     |
+-------------------------------------------------------------------+
[/QUOTE]

Verification