Help to split address into columns

[darrenmccoy]

Hi Experts, Could you help me with formulas to split addresses?

I need to count the spaces from the left and split the street number into column B, then split the street name(s) into column C, then count the spaces from the right and split the the street type into column D.

[TABLE="class: grid, width: 580"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]ADDRESS[/TD]
[TD]STREET NUMBERS[/TD]
[TD]STREET NAME[/TD]
[TD]STREET TYPE[/TD]
[/TR]
[TR]
[TD]478 ST KILDA RD[/TD]
[TD="align: right"]478[/TD]
[TD]ST KILDA[/TD]
[TD]RD[/TD]
[/TR]
[TR]
[TD]23 HAMPTON AVE[/TD]
[TD="align: right"]23[/TD]
[TD]HAMPTON[/TD]
[TD]AVE[/TD]
[/TR]
[TR]
[TD]45 WEST LANGHAM RD[/TD]
[TD="align: right"]45[/TD]
[TD]WEST LANGHAM[/TD]
[TD]RD[/TD]
[/TR]
[TR]
[TD]42 JACKSON ST[/TD]
[TD="align: right"]42[/TD]
[TD]JACKSON[/TD]
[TD]ST[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[lrobbo314]

You can do this with Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitRight = Table.SplitColumn(Source, "ADDRESS", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"ADDRESS.1", "ADDRESS.2"}),
    SplitLeft = Table.SplitColumn(SplitRight, "ADDRESS.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"ADDRESS.1.1", "ADDRESS.1.2"}),
    Rename = Table.RenameColumns(SplitLeft,{{"ADDRESS.1.1", "Street Numbers"}, {"ADDRESS.1.2", "Street Name"}, {"ADDRESS.2", "Street Type"}})
in
    Rename

 

[darrenmccoy]

Thanks, but I dont know how to use power query, I do however know how to use formulas, can you help with that?

 

[DanteAmor]

Try this formulas

Book1
	A	B	C	D
1	ADDRESS	STREET NUMBERS	STREET NAME	STREET TYPE
2	478 ST KILDA RD	478	ST KILDA	RD
3	23 HAMPTON AVE	23	HAMPTON	AVE
4	45 WEST LANGHAM RD	45	WEST LANGHAM	RD
5	42 JACKSON ST	42	JACKSON	ST
Sheet1

Cell Formulas
Range	Formula
B2	=LEFT(SUBSTITUTE(A2," ",REPT(" ",99)),99)+0
C2	=MID(A2,LEN(B2)+2,LEN(A2)-LEN(B2)-LEN(D2)-2)
D2	=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

 

[darrenmccoy]

Try this formulas

	A	B	C	D
478 ST KILDA RD	ST KILDA	RD
23 HAMPTON AVE	HAMPTON	AVE
45 WEST LANGHAM RD	WEST LANGHAM	RD
42 JACKSON ST	JACKSON	ST

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: center"]ADDRESS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: center"]STREET NUMBERS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: center"]STREET NAME[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: center"]STREET TYPE[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]478[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]23[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]45[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]42[/TD]

</tbody>

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=LEFT(SUBSTITUTE(A2," ",REPT(" ",99)),99)+0[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=MID(A2,LEN(B2)+2,LEN(A2)-LEN(B2)-LEN(D2)-2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This works very well, except when I get to an ADDRESS with characters, such as <style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>10/5-7 PRINCES ST it breaks the formula and produces the result
ErrorFunction ADD parameter 1 expects number values. But '1/2A ' is a text and cannot be coerced to a number.

Could you help edit the formula so it supports address with a slash or dash such as:

10/5-7 PRINCES ST
12/25 PRINCES ST
4-5 PRINCES ST
Thank you!

 

[Peter_SSs]

You could try these.

<b>Split Address</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:169px;" /><col style="width:140px;" /><col style="width:127px;" /><col style="width:108px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">ADDRESS</td><td style="font-size:10pt; text-align:right; ">STREET NUMBERS</td><td style="font-size:10pt; ">STREET NAME</td><td style="font-size:10pt; ">STREET TYPE</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">478 ST KILDA RD</td><td style="font-size:10pt; text-align:right; ">478</td><td style="font-size:10pt; ">ST KILDA</td><td style="font-size:10pt; ">RD</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">23 HAMPTON AVE</td><td style="font-size:10pt; text-align:right; ">23</td><td style="font-size:10pt; ">HAMPTON</td><td style="font-size:10pt; ">AVE</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">45 WEST LANGHAM RD</td><td style="font-size:10pt; text-align:right; ">45</td><td style="font-size:10pt; ">WEST LANGHAM</td><td style="font-size:10pt; ">RD</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">42 JACKSON ST</td><td style="font-size:10pt; text-align:right; ">42</td><td style="font-size:10pt; ">JACKSON</td><td style="font-size:10pt; ">ST</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; ">10/5-7 PRINCES ST</td><td style="font-size:10pt; text-align:right; ">10/5-7</td><td style="font-size:10pt; ">PRINCES</td><td style="font-size:10pt; ">ST</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=LEFT(A2,FIND<span style=' color:008000; '>(" ",A2)</span>-1)</td></tr><tr><td >C2</td><td >=MID(A2,LEN<span style=' color:008000; '>(B2)</span>+2,LEN<span style=' color:008000; '>(A2)</span>-LEN<span style=' color:008000; '>(B2&D2)</span>-2)</td></tr><tr><td >D2</td><td >=TRIM(RIGHT<span style=' color:008000; '>(SUBSTITUTE<span style=' color:#0000ff; '>(A2," ",REPT<span style=' color:#ff0000; '>(" ",20)</span>)</span>,20)</span>)</td></tr></table></td></tr></table>

 

[DanteAmor]

Change this

=LEFT(SUBSTITUTE(A2," ",REPT(" ",99)),99)+0

For this

=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

 

[darrenmccoy]

Thank you experts! Appreciate your help. All formulas & suggestions worked.

 

[Peter_SSs]

Thank you experts! Appreciate your help. All formulas & suggestions worked.

You're welcome. Thanks for letting us know.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.