Dividing up cells

K

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
 
B

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

=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
words, so you will need to examine your data.
 
R

robert111

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