Help to split address into columns

darrenmccoy

New Member
Joined
Mar 26, 2019
Messages
21
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.

ADDRESSSTREET NUMBERSSTREET NAMESTREET TYPE
478 ST KILDA RD478ST KILDARD
23 HAMPTON AVE23HAMPTONAVE
45 WEST LANGHAM RD45WEST LANGHAMRD
42 JACKSON ST42JACKSONST

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,758
Office Version
365, 2019, 2016
Platform
Windows
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

New Member
Joined
Mar 26, 2019
Messages
21
Thanks, but I dont know how to use power query, I do however know how to use formulas, can you help with that?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,709
Office Version
2007
Platform
Windows
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

New Member
Joined
Mar 26, 2019
Messages
21
Try this formulas

ABCD
1ADDRESSSTREET NUMBERSSTREET NAMESTREET TYPE
2478 ST KILDA RD478ST KILDARD
323 HAMPTON AVE23HAMPTONAVE
445 WEST LANGHAM RD45WEST LANGHAMRD
542 JACKSON ST42JACKSONST

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


Worksheet Formulas
CellFormula
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"><!--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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,639
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,709
Office Version
2007
Platform
Windows
Change this

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

For this

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,709
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,072
Messages
5,466,430
Members
406,481
Latest member
Dipak543

This Week's Hot Topics

Top