Substring, Contain, Trim, Regex - To isolate Address number and street - Parse Address

[ibesmond]

Hello,

There are a lot of posts on the subject but I can't seem to find a solution that works in my case.

I have located some data so that I can parse the zip and state from a string field.  I did a join to bring in the City but using a zip lookup.

Now I want to trim the address field so I get everything up until the City.

Example Addresses	City
1700 Rooks Road Atchison KS 66002	Atchison
1300 Main Street Atchison KS 66002-0130	Atchison
102 W Main Street Unit 2B Sedan KS 67361	Sedan
15450 S Keeler Street Olathe KS 66062	Olathe
2771 Centennial Road Salina KS 67401-1710	Salina
10 S National Avenue Fort Scott KS 66701	Fort Scott
111 W Locust Avenue El Dorado KS 67042-3513	El Dorado

Basically I want the solution to look at the City column and find it in the example addresses column, and then return the string until it gets to the matching city.  I don't have CASS installed or have a license to Alteryx using the Address Parse tool.  I'm not concerned about separating street addresses lines 1 and 2 so it doens't bother me if they are grouped together.

What is the simplest method for solving?

Substring, Trim (Right trim), Regex tool, Regex_replace formula.

[grossal · Accepted solution]

Hi @ibesmond,

there is probably a cleaner way to do it, but here is how I'd do it:

1) Using a Formula to replace your City within the Address with a |

2) Text to Columns with the Pipe

3) Data Cleansing to remove trailing whitespaces

4) Selecting wanted columns

Output:

Workflow attached. Let me know if I got it right.

Best

Alex

[fmvizcaino]

Hi @ibesmond ,

Attached is a solution showing how to do it with REGEX tool.

I'm using the word Road, Avenue or Street as a start point, without that it would be hard to identify cities with 1 or 2 words.

Best,

Fernando Vizcaino

[Aaron_Harter · Accepted solution]

Hi @ibesmond, you can likely use RegEx to parse the desired part of each address, which would be a bit more efficient, but the following approach may work for you:

[ibesmond]

Hi @fmvizcaino 

I modified the expression to cover additional address naming...

(\d+)\s(.*(?:Road|Street|Avenue|Highway|Parkway|Drive|Terrace|Bypass|Place))\s(.*)\s(KS.*)

I thought this was going to work, but It puts the Street Address 2 in the City Column. Example:

111 W 2nd Street Ste C D & E Hutchinson KS 67501