
Format Phone Number Data for Specific Countries
When team members or potential customers enter phone numbers they often format it differently. This is causing problems with your integrated auto-dialing solution, and generally making the phone number data less searchable and readable.
With the Transform Data module, you can format phone numbers in bulk, and create different templates for each country that can be run on an automated schedule.
Process Summary
- Filter records for a single country.
- Set up functions to format country code and phone number.
- Preview changes in a CSV report.
- Apply updates to the CRM.
- Create templates for each country and add to a recipe to run automatically.
Step-by-Step Instructions
For each country that's important to your business, you'll filter the results and set up formatting rules based on the country code and phone number format. Each of these will be a separate template.
Navigate to the Transform Data module, and in the top menu select the database and record type. Then explore the default templates for a pre-built solution, there are several specific to formatting phone numbers.
Under Step 1, set up a filter to narrow down the database records to show only what you want to change. This ensures you aren't needlessly trying to format records for other countries.
In the example below, the filter will limit records to show only those that have a Country/Region of Australia, and a Phone Number that starts with zero.
Click Search and scroll down to the Record Viewer at the bottom of the screen. You will see all records that match your filter.
In Step 2, you'll set up functions for formatting your phone numbers for country codes.
Select the Phone Number field, then choose one of the available functions to format Australian phone numbers with country codes.
In the example below, the Prefix remove function will remove the local prefix "0" from the front of the values. The Prefix add function will insert the country code, "+61" at the beginning of the phone number. Since these processes are run in the order they are shown, it's important that Prefix Remove happens first, making space for the country code.
Another really helpful option for cleaning up Phone Number values is to use the Remove Non-Digits function. This removes everything that isn't a number – letters, symbols, punctuation, parenthesis, dashes, etc. This can give you a fresh start for your phone number data without losing anything important, making formatting easier.
If you plan on inserting new non-digits, such as adding a "+" before the country code prefix or adding a dash as a separator, the Remove non-digits function should be top of the list so the first step is cleanup, followed by formatting.
The Format phone custom separator function adds spacing based on the number of characters. You can use this to format your phone numbers to match the spacing standards of a specific country.
For example, if you enter "3,1,4" in the Existing Text field, and a dash in the New Text field, a phone number that started as "+61412129757," becomes "+61-4-1212-9757." The "3,1,4" instructs Insycle to enter a dash after 3 characters, another after 1 more, and another after 4 more characters. Any remaining numbers will appear after the final separator.
You can use other separators such as spaces (enter a blank space in the New Text field) or periods.
Here are some examples, using a dash (-) as a separator:
Existing Text Setting |
Original Phone Number |
Modified Phone Number |
---|---|---|
3,1,4 |
+61412129757 |
+61-4-1212-9757 |
3,3 |
2061713177 |
206-171-3177 |
2,3,3 |
+12024446789 |
+1-202-444-6789 |
In combination, with a series of cleanup followed by formatting steps, your formatting rules might look like this:
Preview Changes in the CSV Report
With the filters and functions set up, you can preview the changes. It's important to verify that your formatting is working as expected before those changes are pushed to your live database.
Under Step 3, click the Review button, then select Preview in the popup.
On the Notify tab, you can select recipients for the email report and add additional context to the message. (Make sure to hit Enter after each email address.)
On the When tab, click Run Now and select which records to apply the change to (in most cases this will be All), then click the Run Now button.
Open the CSV file from your email in a spreadsheet application and review the (Before) and (After) values for each field. If the results don't look the way you expected, go back to your filters and functions and try making some adjustments before previewing again.
Apply Changes to the CRM
If everything in your CSV preview looks correct, return to Insycle and move forward with applying the changes to the live CRM data.
Click the Review button under Step 3 and select Update Mode.
On the When tab you can choose to run and apply the update now, or you can set up an automation to run the template on a regular schedule. The first time you apply these changes to the CRM, you should use Run Now.
After you've seen the results in the CRM and you are satisfied with how the operation runs, you can save all of the configurations as a template, and set up automation so this formatting operation runs on a set schedule. This ensures that your phone numbers are consistently formatted on an ongoing basis.
Create templates to format phone numbers for each country that's important to your business. You could create separate templates for priority countries such as, "Format UK Phone Numbers," or "Format New Zealand Phone Numbers."
To schedule all of your phone number formatting templates to automatically run together, use the Recipes feature. Learn more about setting up and scheduling Recipes.
Learn more:
Advanced How Tos
HubSpot users can schedule their international phone number Recipes directly into HubSpot Workflows. To learn more, see Add Insycle Recipes to HubSpot Workflows.
Troubleshooting
HubSpot's phone number formatting features can conflict with Insycle's ability to format phone number fields. If HubSpot's automatic phone number formatting is turned on and you'd like to use Insycle for formatting, you'll need to revert this and un-apply the format in HubSpot.
However, it is not always simple to remove because clicking "Remove" alone will not remove this. You need to also add one space to the phone number for the "Confirm" button to be activated.
Add the space at the end of the phone number field in HubSpot, then click 'Confirm.'
Lastly, make sure to click the Save button.
Frequently Asked Questions
No. This template can serve as a good starting point but will require customization for other countries.
Template Name | Description |
---|---|
Format Phone Number E. 164 |
Formats phone numbers to the E. 164 international standard |
Format Phone Number US (xxx) xxx-xxxx |
Formats USA phone numbers to the (xxx) xxx-xxxx standard |
Format Phone Number US +1 (xxx) xxx-xxxx |
Formats USA phone numbers to the 1+ (xxx) xxx-xxxx standard |
Yes. While you can run the Transform Data module without one, Insycle will analyze your entire database. This is not advisable, as the operations can take a very long time for a large database. Instead, it is easier to filter your data down into small segments and use multiple templates rather than running operations for your entire database.
Additional Resources
Related Help Articles
- Import New Records or Update and Append to Existing from CSV
- Standardize Job Titles, Industries, Location
- Format Names, Phone Numbers, Addresses
- Module Overview: Transform Data
- Bulk Update Values of Any Field
- Add Insycle Recipes to HubSpot Workflows
- Recipes
Related Blog Articles