Hi @Talha Ardic?,
U.S. addresses without comma delimiters between street, city, state, and zip are really tricky to work with! I would recommend tackling this in a three step process:
- Create columns containing the state and zip
- Create a column containing the street only
- Use the SUBSTITUTE function to delete all of the data in your original address column except the city name
Create columns containing the state and zip
It sounds like you've already successfully split state and zip into new columns. That's awesome! Just for reference, I would accomplish this using the "Extract text or patterns" transformation instead of the "Split on text or pattern" transformation. Doing the operation this way allows me to retain the state and zip data in my original column. These two address elements will be needed later on to define the boundaries for the more complex city and street values. If you want to retain your existing "Split on text or pattern" steps, just make a copy of your original column first.
To create columns containing the state and zip, I used the Trifacta patterns for these address elements in my "Extract text or pattern" step. I chose to extract both state and zip as a single element using this pattern:
`{state-abbrev} {zip}`This ensures that I don't accidentally extract a street element that looks like a state (for example, "CT" can represent either "Court" or "Connecticut"), or a street number that looks like a zip code.
My recipe step looks like this in the transform builder:
Create a column containing the street only
Next, I want to create a column that contains the street number and name. Once again, I'm going to use the "Extract text or patterns" transformation to accomplish this. Here's how this step will look in the transform builder:
In this case, I'm going to tell Trifacta to extract data that matches the following pattern:
`{any}+`This will capture any characters. To constrain my matches, I populated the "End extracting before" box with a further pattern:
` {city} {state-abbrev} {zip}`This prevents Trifacta from extracting past the boundary defined by the city, state-abbrev, and zip patterns. Here is a preview of the data extracted by this step:
You might have noticed that Trifacta includes a "city" pattern. However, without comma delimiters in the address we cannot use the "city" pattern to identify city. This is because the "city" pattern is greedy; it will always match 3 or more words separated by spaces unless given additional constraints. If I were to use the "city" pattern directly, I would end up with the following preview:
You can see that the "city" pattern captures words that are actually part of the street name. This is why I chose to use the "city" pattern to define the end of the street name.
Use the SUBSTITUTE function to delete all of the data in your original address column except the city name
Now that we have created columns that contain the street, state, and zip code, we can use the SUBSTITUTE function to replace all of the data in your original column except the city.
You can use the SUBSTITUTE function in either the "Edit with formula" transformation or the "New formula" transformation, depending on whether you want to modify an existing column in your dataset or create a new column. For this example, I picked the "New formula" transformation. Here's what this step looks like in the transform builder:
I essentially perform two find-and-replace operations on the original data using the SUBSTITUTE function. The inner statement finds values in the original data that match the extracted state and zip code, and replaces those values with an empty string. The outer statement searches the result of the inner statement for values that match the extracted street, and replaces those values with an empty string. You're left with the city name.
This is the nested SUBSTITUTE formula that I used:
SUBSTITUTE(SUBSTITUTE(column1, column3, '', false), column2, '', false)To tidy up your column further, you can use the "Trim leading and trailing whitespace" task to remove the extra spaces that remain around your city name. You can find this option in the column menu:
Let me know if this helps!
