Dividing up cells

[koba]

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]

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)-1)

=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.

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

[robert111]

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