seperating an address by street address, unit #, city, state and zip that has no commas

[michnance]

I have a 15,000 line spreadsheet where the addresses are listed with the street address then unit number then city then state code and zip separated by spaces within a single cell. A1 Example:

[TABLE="width: 358"]
<colgroup><col></colgroup><tbody>[TR]
[TD]23 NW 27th Lane Cape Coral FL 33993

I need the address to split like so:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]23 NW 27th Lane[/TD]
[TD]Cape Coral[/TD]
[TD]FL[/TD]
[TD]33993
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
After trying myself I have adding all states listed by two letter state abbreviation in column I starting a row A, if that is helpful in anyway. If not, I can obviously deleted column.

I would so appreciate any help getting this split.

All searches are showing how to do it when there is a comma following street address and another following city. PLEASE HELP! ​

​

 

[sandy666]

with your example:

try PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]adr[/td][td][/td][td=bgcolor:#70AD47]Text Before Delimiter[/td][td=bgcolor:#70AD47]Text Between Delimiters[/td][td=bgcolor:#70AD47]Text Between Delimiters.1[/td][td=bgcolor:#70AD47]Text After Delimiter[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]23 NW 27th Lane Cape Coral FL 33993[/td][td][/td][td=bgcolor:#E2EFDA]23 NW 27th Lane[/td][td=bgcolor:#E2EFDA]Cape Coral[/td][td=bgcolor:#E2EFDA]FL[/td][td=bgcolor:#E2EFDA]33993[/td][/tr]
[/table]

[SIZE=1]// Table10
let
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    Before = Table.AddColumn(Source, "Text Before Delimiter", each Text.BeforeDelimiter([adr], " ", 3), type text),
    Between = Table.AddColumn(Before, "Text Between Delimiters", each Text.BetweenDelimiters([adr], " ", " ", 3, 1), type text),
    Between1 = Table.AddColumn(Between, "Text Between Delimiters.1", each Text.BetweenDelimiters([adr], " ", " ", 5, 0), type text),
    After = Table.AddColumn(Between1, "Text After Delimiter", each Text.AfterDelimiter([adr], " ", {0, RelativePosition.FromEnd}), type text),
    #"Removed Columns" = Table.RemoveColumns(After,{"adr"})
in
    #"Removed Columns"[/SIZE]

column names can be changed of course, it's cosmetics

 

[michnance]

with your example:

Sandy666:
Thank you SO much for your reply. I truly hate to ask another question but I've never used a PowerQuery before. Can you tell me where I am supposed to enter the code you provided? Again, I truly appreciate all of your help!

 

[sandy666]

too much typing
here is excel file: example

Data - Show Queries - dbl click on table there - Ribbon - Advanced Editor - and you'll see the same M-code as in the post

 

[alansidman]

https://excel.solutions/2017/11/power-query-paste-code-video/

 

[Rick Rothstein]

I am not sure what Sandy's solution will do (I do not do PowerQuery), but to my way of thinking what you want to do will be near impossible. The problem stems from the fact that street names and city names can be composed of multiple "words" in any combination. Without a delimiter, there is no simple way to figure out where the street name ends and the city name begins.

 

[sandy666]

Right Rick, but I bolded: with your example (btw. example is not representative).

 

[michnance]

too much typing

sandy666,
Thank you so much! I have never done anything as advanced as a power query. I am not sure if this is allowed on this forum but is there any chance you would connect with me outside of this site? email or phone? happy to pay you for your help! lmk!!!

 

[sandy666]

forum rules #4

 

[michnance]

thank you so much for your reply. i know a comma after street address would change this entire situation for me! thanks for your input!!!