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.
Solved! Go to 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
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
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:
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