Dividing up cells

  • Thread starter Thread starter koba
  • Start date Start date
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
 
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.
 
you could look for words like street, drive, road, avenue, way, close,
and use these to decide where to split your address field.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IF/THEN - Number Range 3
Telephone book 1
Adding one word onto the end of text a column of cells 2
MS Excel Data Validation, Ignore Blanks in List 4
Not sure how to do this, not even sure how to ask the question clearly... 4
Formula Format problem - I think 1
Parsing line breaks into separate cells 3
Sorting/Moving 3

Back
Top