How to get city from proper address format in excel

Question

A lot of the tutorial to get city in the internet is with the address already separated by comma, which is not the case for me

the address is often written

123 Homer st Chicago, IL 121212

or more complicated

1212 State Road 33 Seattle, WA 80124

how to get city?

Answer 1

So this is the closest I get to get city . With adding reverse function in VBA and this works only for City with one word

Add reverse function:

Step1. Open VBA = ALT + F11
Step2. Right click - insert module
Paste this code
Function Reverse(str As String) As String
    Reverse = StrReverse(Trim(str))
End Function

And this is the excel formula

=MID(A2,FIND(",",A2) -FIND(" ",Reverse(LEFT(A2,FIND(",",A2))))+2,FIND(" ",Reverse(LEFT(A2,FIND(",",A2))))-2)

For city with more than one word. it need to get manually fixed. I guess this only work with smaller sample set

For extra clean up, we can use Zip Code to get city . and highlight those zipcode with multiple cities for additional clean up

Answer 2

The ease of this task will very much depend on the cleanliness of the data.

That said, here's a way to join an address to a list of cities using a partial string match. This will not be perfect, but it may help get you closer to your goal.

First, create a query to get a list of cities. This is a list of 200 US cities by population:

let
    Source = Web.Page(Web.Contents("https://worldpopulationreview.com/us-cities")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Rank", Int64.Type}, {"Name", type text}, {"State", type text}, {"2020 Pop", Int64.Type}, {"2010 Census", Int64.Type}, {"Change", Percentage.Type}, {"Density (km²)", Int64.Type}, {"Area (km²)", Int64.Type}})
in
    #"Changed Type"

To use this code, use Data>Get & Transform Data>Get Data>From Other Sources>Blank Query.

When the Power Query Editor opens, paste the code above into the Advanced Editor, which you can access from the ribbon on the Home tab. In the Query Settings, Name the Query Top200USCities.

When you've done that, using Close & Load will put the list into your workbook:

This table will act as a reference. In another sheet, I have your example addresses and a couple of others:

I first create a query on that range by using Data>Get & Transform DAta>From Table/Range.

Then I use Add Column>Custom Column to define a new column called LookupCity, like this:

Table.SelectRows(
        Top200USCities,
        (lookup) => Text.Contains(Text.Lower([Address]),Text.Lower(lookup[Name]))
        )

Like this:

After doing that, you'll see a new column next to your address with the word Table in every row. If you click the double-arrow in the top right corner of that column, you can select columns from the lookup table to add to your address table. I'll add Name, State and 2020 Pop.

The results look like this:

Rows 1, 2 and 3 are correct. Row 4 has matched the road name with the city - Chicago and row 5 has matched with Los Angeles the city even though the city is Toronto.

At this point, you could stop, load the data back into your workbook, then resolve any errors by hand. Or, you could try to get a bit closer with a few more steps.

Suppose we can say that each city name ends with a comma. If that's true, then using this in the LookupCity Custom Column would make it more accurate:

Table.SelectRows(
        Top200USCities,
        (lookup) => Text.Contains(Text.Lower([Address]),Text.Lower(lookup[Name]&","))
        )

Now we are saying "Does the address contain this city ending with a comma?" The results are much better:

This is all well and good if your address list contains fully formed and correctly spelled city names. If it doesn't, then you can try manually adding city name variations to the list of cities. If you already have Saint Louis, but your data contains St. Louis, St Louis, Saint Louis and STL, then you can add the others to the lookup table while duplicating the other metadata should that be interesting. Like this:

As you add more variations of city names to the list, your query will "catch" more and more of the addresses.

Good luck.