Separating Address Blocks

[Lee.]

Hi,
Does anyone know a quick way to separate an address which is in one cell, into each individual line?

I can't really use text-to-columns I don't think as the spaces are uneven and also it would break up the street names etc.

The existing data is text wrapped rather than each line of the address being entered on a new line

Sample

Excel 2012
	A
1	Address
2	14 Taylor St St. Stephens Ward Kent CT2 7PP
3	5 Binney St Abbey Ward Buckinghamshire HP11 2AX
4	8 Moor Place East Southbourne and Tuckton W Bournemouth BH6 3BE
5	505 Exeter Rd Hawerby cum Beesby Lincolnshire DN36 5RP
6	5396 Forth Street Greets Green and Lyng Ward West Midlands B70 9DT
7	9472 Lind St Desborough Northamptonshire NN14 2GH
8	7457 Cowl St #70 Bargate Ward Southampton SO14 3TY
Sheet1

Ideal layout

Excel 2012
	D	E	F	G
1	address	city	county	postal
2	14 Taylor St	St. Stephens Ward	Kent	CT2 7PP
3	5 Binney St	Abbey Ward	Buckinghamshire	HP11 2AX
4	8 Moor Place	East Southbourne and Tuckton W	Bournemouth	BH6 3BE
5	505 Exeter Rd	Hawerby cum Beesby	Lincolnshire	DN36 5RP
6	5396 Forth Street	Greets Green and Lyng Ward	West Midlands	B70 9DT
7	9472 Lind St	Desborough	Northamptonshire	NN14 2GH
8	7457 Cowl St #70	Bargate Ward	Southampton	SO14 3TY
uk-500

Thanks

 

[BrianM]

Well, I don't think is much of any help; but, I was able to get the Address and Postal parsed. It's the City and County that are tough (for me anyways)...

Address: =IF(ISERROR(FIND("#",A2)),LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),3))-1),LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),4))-1))
Postal: =MID(A2,SEARCH("@",SUBSTITUTE(A2," ","@",LEN(A2)-1-LEN(SUBSTITUTE(A2," ",""))))+1,LEN(A2))

Bill Jelen - https://www.youtube.com/watch?v=yZE-njl7fOE
Aladin - http://www.mrexcel.com/forum/excel-...earch-=find-work-right-left-2.html#post126578

 

[BrianM]

Ok, I tried again... prolly convoluted; but... I used the fact that you have the three spaces before the County to parse it; so, you may need to keep those spaces. I originally thought you should change them to one space for continuity. But, it actually can be leveraged (if it is always the case anyway).

City: =MID(A2,LEN(IF(ISERROR(FIND("#",A2)),LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),3))-1),LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),4))-1)))+2,LEN(A2)-(LEN(D2)+LEN(E2)+LEN(B2))-3)

County: =MID(A2,FIND(" ",A2)+3,LEN(A2)-7-3-FIND(" ",A2))

Also, the addition of the LEN()'s for City is probably not the best way to do it; but, the formula is not three times longer than it should be either. Lastly, sometimes you have three spaces in front of the City, which may lead to a blank as the first character post parse; that to could be corrected with the formula; but, it would be a big formula as well.

Anyway, I hope I helped a bit...

Brian

 

[BrianM]

It took me a long time to get the Htmlmaker to add in; I kept getting errors. But, I thought it was a good idea that I add the sheet in .html format...

Also, I changed County: to =MID(A2,FIND(" ",A2)+3,(LEN(A2)-LEN(E2))-3-FIND(" ",A2))

Brian

Excel 2012
	A	B	C	D	E
1	Unparsed Data	Address	City	County	Postal
2	14 Taylor St St. Stephens Ward Kent CT2 7PP	14 Taylor St	St. Stephens Ward	Kent	CT2 7PP
3	5 Binney St Abbey Ward Buckinghamshire HP11 2AX	5 Binney St	Abbey Ward	Buckinghamshire	HP11 2AX
4	8 Moor Place East Southbourne and Tuckton W Bournemouth BH6 3BE	8 Moor Place	East Southbourne and Tuckton W	Bournemouth	BH6 3BE
5	505 Exeter Rd Hawerby cum Beesby Lincolnshire DN36 5RP	505 Exeter Rd	Hawerby cum Beesby	Lincolnshire	DN36 5RP
6	5396 Forth Street Greets Green and Lyng Ward West Midlands B70 9DT	5396 Forth Street	Greets Green and Lyng Ward	West Midlands	B70 9DT
7	9472 Lind St Desborough Northamptonshire NN14 2GH	9472 Lind St	Desborough	Northamptonshire	NN14 2GH
8	7457 Cowl St #70 Bargate Ward Southampton SO14 3TY	7457 Cowl St #70	Bargate Ward	Southampton	SO14 3TY
Sheet1

Cell Formulas
Range	Formula
B2	=IF(ISERROR(FIND("#",A2)),LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),3))-1),LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),4))-1))
C2	=MID(A2,LEN(IF(ISERROR(FIND("#",A2)),LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),3))-1),LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),4))-1)))+2,LEN(A2)-(LEN(D2)+LEN(E2)+LEN(B2))-3)
D2	=MID(A2,FIND(" ",A2)+3,(LEN(A2)-LEN(E2))-3-FIND(" ",A2))
E2	=MID(A2,SEARCH("@",SUBSTITUTE(A2," ","@",LEN(A2)-1-LEN(SUBSTITUTE(A2," ",""))))+1,LEN(A2))

 

[Lee.]

Thank you for this, it is really appreciated. It has helped me greatly and I won't need to manually go through over 10,000 lines of data

It has helped me reduce this significantly