Extract UK address into seperate cells

[jamesjhhill]

I hope someone can help me.

I have c 500 address which i need to sepearte into sepearte cells for the purpose of a mail merge.

I have attached an excel spreadsheet with all the addresses in and have managed to extract the first line of the address using the LEFT function.

I reckon i can work out how to get the postocde but dont know how to seperate the middle (towns, counties) etc.

Hope you can help.

[davegugg]

Try this in C1, copy down:

=LEFT(A1,FIND(",",A1,1)-1)

[jamesjhhill]

Thanks davegugg,

This is the forumla i used to extract the first line of the address (which i already have). I am trying to find a formula which would let me extract the second line, town, county and post code. So for example, C1 = 32 Oak Mead, D1 = Farncombe, E1 = Godalming, F1 = GU7 3RJ.

I am sure there is a simple way to do this because i've managed it before but i just cant work it out!

[NBVC]

Try, in C1:

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1&",",""),F1,""))

You can hide this column

in D1:

=IFERROR(LEFT(C1,FIND(",",C1)-1),"")

in E1:

=IFERROR(TRIM(SUBSTITUTE(MID(C1,FIND(",",C1)+1,255),",","")),"")

in F1:

=TRIM(MID(A1,FIND("^^",SUBSTITUTE(A1," ","^^",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))),100))

each copied down.

or if you don't want the hidden comma, then in C1:

=IFERROR(LEFT(TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1&",",""),E1,"")),FIND(",",TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1&",",""),E1,"")))-1),"")

in D1:

=IFERROR(TRIM(SUBSTITUTE(MID(TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1&",",""),E1,"")),FIND(",",TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1&",",""),E1,"")))+1,255),",","")),"")

in E1:

=TRIM(MID(A1,FIND("^^",SUBSTITUTE(A1," ","^^",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))),100))

[davegugg]

I see NBVC has a solution, but here's how I would have evolved my original response to provide a solution:

In C1: =LEFT(A1,FIND(",",A1,1)-1)

In D1: =IF(ISERROR(MID(A1,LEN(C1)+3,FIND(",",A1,LEN(C1)+3)-(LEN(C1)+3))),RIGHT(A1,LEN(A1)-LEN(C1)-2),MID(A1,LEN(C1)+3,FIND(",",A1,LEN(C1)+3)-(LEN(C1)+3)))

In E1: =IF(ISERROR(MID(A1,LEN(D1)+LEN(C1)+5,FIND(",",RIGHT(A1,LEN(A1)-(LEN(D1)+LEN(C1)+5))))),"",MID(A1,LEN(D1)+LEN(C1)+5,FIND(",",RIGHT(A1,LEN(A1)-(LEN(D1)+LEN(C1)+5)))))

In F1: =IF(ISERROR(RIGHT(A1,LEN(A1)-FIND(",",A1,(LEN(C1)+LEN(D1)+LEN(E1)))-1)),"",IF(E1="","",(RIGHT(A1,LEN(A1)-FIND(",",A1,(LEN(C1)+LEN(D1)+LEN(E1)))-1))))

And the moral of this thread is ALWAYS separate your data to begin with when you have the option. It's much easier to join data from different columns than to separate a single column into different pieces (street address, city, zip, etc.)

[Marcol]

There is a bit more to your problem than meets the eye due to inconsistancies in your data.

Try this
1/. Use Text to Columns to spilt the initial string in "Sheet1" Column A
Select Column A then Data > Text to Columns Choose:= Delimited ... Next >
Check:= Comma ... Next>
In the Destination Field Change $A$1 to $B$1 ... Finish

2/. With "Sheet1"
In I1

Please Login or Register  to view this content.

In J1

Please Login or Register  to view this content.

Drag/Fill both Down

Note:= There is no easy way to tell what is a county and what is a city/town, so column I is a mixture of towns/cities and counties.

3/ With "Sheet2"
In A1

Please Login or Register  to view this content.

Drag Across to Column G Then Down

In H1

Please Login or Register  to view this content.

Drag Across to Column J Then Down

4/. You can now Copy "Sheet2" and Paste Values to wherever.

This is not perfect, but I think it's a fair way towards getting the result you need.

[Marcol]

For some reason the Go Advanced button seems to be broken when trying to edit a post, so I can't change the attachment in the previous post

I have added NBVCs' solution to this workbook so you can compare the results.