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?

  • Are all addresses formatted in this way or do some of them contain other separators prior to the city name? Also, is the address limited to a list of major cities or does it cover all municipalities of any size in the US? Are there addresses in other countries? – FlexYourData Sep 3 '20 at 17:54
  • You really can't do this reliably, since there is no separator beetween the street and the city. I'd think the most reliable way is with a lookup on the ZipCode. Either use an online API (eg: zip-codes.com), an API from the USPS, or purchase a database. Online lookups will be relatively slow as you have to do one call per zip code. Depending on your requirements, this may not be an issue. – Ron Rosenfeld Sep 4 '20 at 0:47

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

  • 1
    There are many cities that have two or more word names: Saint Louis,Salt Lake City. This assumes that the "City" only have one word name. – Scott Craner Sep 3 '20 at 16:54
  • Assuming your setup and one word city: =SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s[contains(.,',')]"),",","") no need for vba. – Scott Craner Sep 3 '20 at 18:02

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:

    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}})
    #"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:

enter image description here

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

enter image description here

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:

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

Like this:

enter image description here

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:

enter image description here

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:

        (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:

enter image description here

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:

enter image description here

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

Good luck.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.