CODEWITHSUNDEEP
regexexcel
Parseltongue
Parseltongue

Reputation: 11657

Extract substring starting with the comma, moving right, until you hit a space

I have a series of addresses in one column. I am trying to extract each component (Street Address, City, State, and Zip Code) into a separate columns.

I was able to extract the zip codes rather easily with `=RIGHT(A1, 5)'. However, I am having a hard time extracting the city. All of the rows follow the same format below. My idea is to find the comma, and extract the substring from right to left until getting a space. How do I do this?

Here is an example of what the data looks like: 

2209 Fake Street Arlington, TX 76015
3100 Fake Street Bedford, TX 76021
3558 Fake Street Flower Mound, TX 75028
4230 Fake Street Fort Worth, TX 76119
2662 Fake Street Bedford, TX 76021

Upvotes: 1

Views: 100

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

That will only work with cities that have one word. And looking for the type of address (road, street, etc) for the start of the city name, won't work when there is no type. I think if your layout has no unique separator between street and city, you'll probably need a zip code lookup table to get the city.

In addition, you will need code to resolve issues where two different cities have the same zip code. For example, in Texas, 76119 could refer to FORT WORTH, FOREST HILL, or FT WORTH. And you may need code to handle misspellings.

It might be that these are few enough to allow manual correction.

Upvotes: 2

Related Questions