Extract city from address

Hi,

I have data which has US addresses but there is no order in the dataset. As in there is no clear distinction as to which is city, which is street name because of no delimiters present in the address.

The State is at the end denoted by just two letters like TX for texas. The city is before that but again it is not just one word, it can be more than one word.

How can i extract city name from this?

Example:- 1234 XXXXX El Paso TX

11 XXX XXXX Garland TX

Regex

Data Investigation

Accepted answers

Qiu

@RAJ_12

Sometimes, Brutal force is beauty.

I managed to download a list of world cities then solved the puzzle.

0122-RAJ_12.yxzp

All comments

atcodedog05

Hi @RAJ_12

Without any exact pattern its hard to find the city. A thing what you can do is have a look up table with cities and do a find on address.

Hope this helps 🙂

JDong

@RAJ_12 @atcodedog05

I just split text to columns to start on a result...but maybe something can be done using Regex ?

Again to identify cities with more than one word...happy to see more responses.

City.yxmd

Qiu

@RAJ_12

Sometimes, Brutal force is beauty.

I managed to download a list of world cities then solved the puzzle.

0122-RAJ_12.yxzp

BretCarr

The main thing is that there has to be a delimiter before the city. You should use regex and keep adding to the OR statement I started for you. It is the part with the pipes:

(?<houseNumber>\d+) (?<streetName>.* (?:Rd|Blvd|Ave|St|Street|Avenue|Place|Pl|Parkway|Pkwy)) (?<city>[\w ]*) (?<state>[A-Z]{2})

I name my capture ranges so it helps breakup the expression for legibility.

Let me know how it goes!

atcodedog05

Hi @RAJ_12

@Qiu 's approach of find and replace should work for your scenario provided they have the official names. Why dont you give it a try.

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167