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


--
koba
------------------------------------------------------------------------
koba's Profile: http://www.excelforum.com/member.php...o&userid=28639
View this thread: http://www.excelforum.com/showthread...hreadid=529904

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

you could look for words like street, drive, road, avenue, way, close,
and use these to decide where to split your address field.


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=529904