Help to split address into columns

darrenmccoy · Nov 6, 2019

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.

ADDRESS	STREET NUMBERS	STREET NAME	STREET TYPE
478 ST KILDA RD	478	ST KILDA	RD
23 HAMPTON AVE	23	HAMPTON	AVE
45 WEST LANGHAM RD	45	WEST LANGHAM	RD
42 JACKSON ST	42	JACKSON	ST

lrobbo314 · Nov 6, 2019

You can do this with Power Query.

Code:

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 · Nov 6, 2019

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

DanteAmor · Nov 6, 2019

Try this formulas

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">ADDRESS</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">STREET NUMBERS</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">STREET NAME</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">STREET TYPE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">478 ST KILDA RD</td><td style="text-align: right;;">478</td><td style=";">ST KILDA</td><td style=";">RD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">23 HAMPTON AVE</td><td style="text-align: right;;">23</td><td style=";">HAMPTON</td><td style=";">AVE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">45 WEST LANGHAM RD</td><td style="text-align: right;;">45</td><td style=";">WEST LANGHAM</td><td style=";">RD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">42 JACKSON ST</td><td style="text-align: right;;">42</td><td style=";">JACKSON</td><td style=";">ST</td></tr></tbody></table><p style="width:4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)"></p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=LEFT(<font color="Blue">SUBSTITUTE(<font color="Red">A2," ",REPT(<font color="Green">" ",99</font>)</font>),99</font>)+0</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=MID(<font color="Blue">A2,LEN(<font color="Red">B2</font>)+2,LEN(<font color="Red">A2</font>)-LEN(<font color="Red">B2</font>)-LEN(<font color="Red">D2</font>)-2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=TRIM(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">A2," ",REPT(<font color="Purple">" ",99</font>)</font>),99</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

darrenmccoy · Nov 6, 2019

DanteAmor said:
Try this formulas

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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Worksheet Formulas

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

This works very well, except when I get to an ADDRESS with characters, such as <style type="text/css"></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 · Nov 6, 2019

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 · Nov 6, 2019

Change this

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

For this

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

darrenmccoy · Nov 6, 2019

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

Peter_SSs · Nov 6, 2019

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

You're welcome. Thanks for letting us know.

DanteAmor · Nov 6, 2019

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

Help to split address into columns

darrenmccoy

New Member

lrobbo314

Well-known Member

darrenmccoy

New Member

DanteAmor

Well-known Member

darrenmccoy

New Member

Peter_SSs

MrExcel MVP, Moderator

DanteAmor

Well-known Member

darrenmccoy

New Member

Peter_SSs

MrExcel MVP, Moderator

DanteAmor

Well-known Member

Forum statistics

Share this page

Some videos you may like

This Week's Hot Topics