Separating a full address into separate fields can be useful for personalization, segmentation, and data analysis.
Without that separation, segmenting by those attributes is not possible.
With Insycle, you can easily separate all fields from a full address.
How It Works
With Insycle, you can use the Transform Data module to analyze full addresses, copy the relevant portions of the address, and move them to their own individual fields.
Insycle does this by analyzing where the commas in the address are, copying the relevant portion of the address, and moving it to a specified field.
Insycle offers a pre-built recipe, "Extract Address Fields from Full Address, which features four individual templates for extracting street addresses, cities, states, and zip codes, then moving them to the correct field.
All templates work in a similar way:
- Step 1: Filter records down to only those that have the correct format to work with the template.
- Step 2: Instruct Insycle to identify the relevant part of the address, using the comma placements.
- Step 3: Copy the selected data into the new field.
Insycle's Pre-Built Recipe for Street Address Separation
A template is a process of steps and settings that are executed at the module level. For instance, you might have a template for merging duplicate companies by name and domain name.
Recipes are a collection of templates, ordered into numbered steps, which can then be executed in succession. You can add pre-built or custom templates to a Recipe.
Insycle offers a pre-built recipe, "Extract Address Fields from Full Address, which features four individual templates for extracting street addresses, cities, states, and zip codes, then moving them to the correct field. You can navigate to this recipe by selecting Recipes from the left-hand navigational menu, then selecting the Recipe name from the dropdown menu on the page.
All four templates work similarly, from the Transform Data module.
Learn More: Recipes
Step 1: Filter Records
Let's break down how this works using the City field, then show you all four templates for street address, city, state, zip code.
Open the Transform Data module, then the "Extract Address Fields from Full Address," template.
In the first step, we filter our records.
This filters your records down to those:
- That have data in the Street Address field
- That have the correct standard format and comma placement within the street address
Addresses that do not have have the standard format will not be included in this operation (for example, a street address that is missing commas).
Step 2: Identify Address Portions Using Commas
Now, we tell Insycle how to identify the city contained within the full address, by telling it where to look between the commas. Then, we copy that data over to the City field.
This is done using the "Split by any delimiter and keep just selected term," function.
In the Existing Text input, we put a comma. This tells Insycle that we want to look in between the commas.
In the New Text in put, we put 2. This tells Insycle where to look between the commas. The 2 refers to the second block of data located between the commas.
To do street address, you'd enter 1 into the New text input. City would be 2.
Separating State and Zip Codes From Full Addresses
State and Zip code have an additional wrinkle to consider, because there is no comma between the state and zip code. For example, "NY 10012."
To identify state, we must add an additional "Split by any delimiter and keep just selected term," function, this time splitting the State and Zip Code from each other using the space between them.
You can't see it in this screenshot, but in the second Existing Text input there is a space. Like " ".
The "1" in the New Text Field tells Insycle to look before the space in the selected data. The end result looks like this:
Here is what the template would look like for separating the zip code:
Step 3: Copy The Selected Data Into a New Field
Finally, we copy the data that we have selected using the "Split by any delimiter and keep just selected term," function into the field we would like to store it in.
This does not alter the original field (Street Address), but does place the city data inside of the City field.
Audit Trail and History
The Activity Tracker lets you review all changes made through Insycle. At any time you can download a CSV report of the operation and records affected, including during imports.
Related Blog Articles
- Standardize State & Country Fields That Break HubSpot Salesforce Sync
- Find & Fix CRM Data Issues using the Data Health Assessment Tool
- Declutter Your CRM By Purging Low-Quality Data Automatically
- How to Create a Customer Data Management Plan
- 5 Common HubSpot Data Quality Issues and How to Fix Them
Related Help Articles
- Copy or Move Values Between Fields
- Customer Data Health Assessment
- Activity Tracker