Split out Unabbreviated State from City and State field

[LarryAlx]

Hello all,

I have a column which has city and state.  The state is not abbreviated which makes it difficult to parse the state out using something like

Text.AfterDelimiter([CityState]," ",{0, RelativePosition.FromEnd})

, for something like ohio, since sometimes it would need to be 

Text.AfterDelimiter([CityState]," ",{1, RelativePosition.FromEnd})

, like in the case of new york.

I'm hoping you all may have seen this in the past and have a way of handling this.

Sample of what I need to be able to split into city and state:

CityState	City	State
west chester ohio	west chester	ohio
akron ohio	akron	ohio
blue ash ohio	blue ash	ohio
north bend ohio	north bend	ohio
suffern new york	suffern	new york
miami beach florida	miami beach	florida
dry ridge kentucky	dry ridge	kentucky
charlotte north carolina	charlotte	north carolina
selma north carolina	selma	north carolina
fort mitchell kentucky	fort mitchell	kentucky
lakewood new jersey	lakewood	new jersey
brooklyn new york	brooklyn	new york
villa hills kentucky	villa hills	kentucky
black mountain north carolina	black mountain	north carolina
oak brook illinois	oak brook	illinois

My guess is that I could possible do some sort of condition using a seperate table that houses all the possible states but I'm not sure what it would need to look like.

[collinsg · Accepted solution]

Good day LarryAlx,

I think you pretty much have the solution. Knitting together the pieces you outline:

1. Create a list containing the name of each state.
2. Add a column for state: if the last word in CityState is in the list of states use it as the name of the state, otherwise use the last two words in CityState as the state. This depends on states having at most two words in their name.
3. Add a column for city: copy and replace the text of the state within CityState with "".

I started with these tables in an Excel workbook.

...and created these two queries

// List of States
let
  Source = Excel.Workbook(File.Contents("C:\Book1.xlsx"), null, true),
  States_Table = Source{[Item="States",Kind="Table"]}[Data],
  #"Changed Type" = Table.TransformColumnTypes(States_Table,{{"State", type text}}),
  State = #"Changed Type"[State]
in
  State

// States and Cities
let
  Source = Excel.Workbook(File.Contents("C:\Book1.xlsx"), null, true),
  tbl_Table = Source{[Item = "CityStates", Kind = "Table"]}[Data],
  #"Changed Type" = Table.TransformColumnTypes(tbl_Table, {{"CityState", type text}}),
  #"Added State" = Table.AddColumn(
    #"Changed Type",
    "State",
    each
      if List.Contains(#"List of States", Text.AfterDelimiter([CityState], " ", {0, RelativePosition.FromEnd})) then
        Text.AfterDelimiter([CityState], " ", {0, RelativePosition.FromEnd})
      else
        Text.AfterDelimiter([CityState], " ", {1, RelativePosition.FromEnd})
    , type text),
  #"Added City" = Table.AddColumn(#"Added State", "City", each Text.Replace([CityState] as nullable text, [State] as text, "" as text), type text)
in
  #"Added City"

 yielding the result

Hope this helps.

[LarryAlx]

Thanks this definitely worked.

[Syndicate_Admin]

I want to see you try this with Virginia and West Virginia...

[LarryAlx]

You think your funny, mr funny man. 

You are right though and although it may not be optimal, I'll probably create 2 lists one ListOfStates and the other ListOfStatesTwoPart and run through the latter first and then check the former second, since I need to add an else if to this anyways for anything that does not have a state.

Appreciate you bringing it to light.

[Syndicate_Admin]

I think you can solve that by changing the order in which you search for strings. You need to first search for West Virginia and then for just Virginia, etc.