How to: Extract a City from a full address

Hello Honey Code!

I am trying to find a formula that can be used within my workbook to extract the city from a full address.

Example: 123 Main St Seattle, Washington 12345
Formula returns: Seattle.

Thank you

Hey there!

If you have control over the format of your address, you could add a delimiting comma after the street address, then fetch the text between the first two instances of commas, like below:

image

Formula used:

=MID(C4,SEARCH(", ",C4)+1,SEARCH(",",C4,SEARCH(", ",C4)+1)-SEARCH(", ",C4)-1)

Alternatively you could get the index of the end of the street address by instead searching for every possible ending ("St", "Rd", "Ave", etc.)

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.