One method,
=LEFT(A1,FIND(" ",MID(A1,(FIND(" ",MID(A1,FIND(" ",A1)+1,99))+FIND("
",A1)+1),99))+FIND(" ",MID(A1,FIND(" ",A1)+1,99))+FIND(" ",A1))
=MID(A1,FIND(" ",MID(A1,(FIND(" ",MID(A1,FIND(" ",A1)+1,99))+FIND("
",A1)+1),99))+FIND(" ",MID(A1,FIND(" ",A1)+1,99))+FIND(" ",A1)+1,99)
but I would doubt that all of your addresses would split evenly on 3
words, so you will need to examine your data.
koba Wrote:
Hi All
I have a cell with the following text
251 High St Kangaroo Flat VIC 3555
Using a formula How to i divide it up at the thrid word so that in one
cell is
251 High St
and in the other is
Kangaroo Flat VIC 3555
I have a lot of address and need to mail merge them into word
Thanks
Andrew
--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=529904