Help to split address into columns

darrenmccoy

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

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,511
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
17
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
7,969
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
17
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
41,826
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
7,969
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
7,969
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,078,515
Messages
5,340,863
Members
399,396
Latest member
PBE

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top