Format Inconsistent Phone Numbers with Country Codes That Break Auto-Dialers

When team members or potential customers enter phone numbers, they may format it in dozens of ways. This can cause problems with the CRM working with your integrated auto-dialing solution and hurt the searchability and readability of your phone number data.

Use Insycle to automatically format phone numbers, and create different templates for each country that can be run on a set schedule.

Process Summary 

  1. Filter phone numbers for a single country.
  2. Set up functions to format country code and phone number.
  3. Preview changes in a CSV report.
  4. Apply updates to the CRM.
  5. Create custom templates for each country.
  6. Add templates to a recipe to run them automatically.

 

Step-by-Step Instructions

Step 1: Filter Phone Numbers for a Single Country

Navigate to the Transform Data module, this is where we will update the Phone Number field formats that include international country codes. Select the data type from the top menu.

First, set the filter. The filter identifies the records to update with this operation. For example, if formatting phone numbers for Australia, you'd add that rule here. This ensures you only update the relevant records. When you build templates for other countries, you will alter this filter to reflect those countries.

filter australia

Click the Search button and Insycle will generate a preview of the records that meet this filter at the bottom of the page.

Step 2: Set Up Functions to Format Country Code and Phone Number

Select the Phone Number field, then choose one of the available functions to format Australian phone numbers with country codes.

First, we can inject the country code at the beginning of the phone number, using the Prefix add function. We'll also remove the local number "0" from the front of these numbers using the Prefix Remove function. Since these processes are run in the order they are shown, we will make sure Prefix Remove runs first.

This will add the country code at the beginning of all phone numbers that match your filter. This is the quick-and-easy way to inject country codes.

prefix remove prefix add

You can also use functions to further clean the field, such as using the Remove Non-Digits, which removes everything that isn't a number — letters, symbols, punctuation, parenthesis, dashes, etc. The Remove Non-Digits function serves as a fresh start for your phone number data, without losing anything important.

Remember, this will clear existing formatting. If you plan on injecting new non-digits with the template, such as adding a "+61" country code prefix or adding a dash as a separator, the Remove non-digits function needs to appear first in the list.

Using Remove non-digits is often a requirement before you format your phone numbers. That's because phone numbers may include spaces, periods, dashes, or other random characters that make them impossible to format. By removing everything except digits, you ensure that your data is as clean and consistent as possible before formatting.

Now we add in the Format phone custom separator and enter "3,1,4" in the Existing Text Field, and a dash (-) in the New Text field.

format phone cusotm separator

The Format phone customer separator function formats the phone number for spacing based on the number of characters. For instance, using the "3,1,4" input and a dash, with the country code added through Prefix Add "412129757" becomes → "+61-4-1212-9757."

The numbers show after how many characters the separator will appear.

In "3,1,4" the first separator appears after three characters, then after one more character, then after four. Any remaining numbers will appear after the final separator. You can use other separators too such as spaces (add a blank space to the "New Text" field), periods, stars, or any separator that makes sense.

Here are some examples, using a dash (-) as a separator:

Format Phone Custom Separator Existing Text Input

Original Phone Number

After Custom Phone Separator

3,1,4

+61412129757

+61-4-1212-9757

3,3

2061713177

206-171-3177

2,3,3

+12024446789

+1-202-444-6789

You can use this to format your phone numbers to the separation and spacing standards of your chosen country.

If you'd like to add a "0" instead of the country code at the beginning of the phone number, you can do that using the Prefix replace function.

functions

 

Preview Changes in CSV Report

Now with the filters and functions set up, you can preview the changes that you are making to your data. That way, you can check to ensure your standardization template is working as expected before those changes are pushed to your live database. 

First, under Step 3 click the Review button.

review button

In the Transform Data popup, select the Preview option.

preview mode or update mode

On the Notify tab, add any additional recipients who should receive the CSV (and make sure to hit Enter after each address). 

mceclip0.png

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 Phone Number (Before) and Phone Number (After) columns. 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, then click Next.

transform data preview or update

Enter any additional recipients or additional context on the Notify tab.

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 basis. The first time you are applying these changes to the CRM, we suggest you use Run Now.

In the Run Now tab, you have control over how many records you can run the update on. You may want to test it on a small number of records, choose All and click Run Now.

run now
Create Templates for All Countries

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 for future use. On the Template menu at the top of the page click the + and give it a unique name, then click the Save button.

mceclip1.png

You can create templates to format phone numbers for each country that's important to your business, based on the country code in the phone number, or using the contact's "Country" field. For example, you might create separate templates for priority countries—"Format UK Phone Numbers", or "Format New Zealand Phone Numbers."

Repeat Steps 1, 2, Preview, Update and Save Template for each country.

Single Template Automation

You can schedule your Transform Data templates to run on an automated, set schedule. Return to the Review flow under Step 3. Select Update mode, and on the When tab, under Automate, configure the template to run on a set schedule.

automate or run now

You can also schedule formatting automation using Recipes, which are a collection of templates run together. You can view all scheduled automations on the “Automations” page on your dashboard. HubSpot users can also automate using HubSpot Workflows.

Learn more about Automation and Manual Operations.

Add All Templates to a Recipe and Schedule Automation

Once you've created all of your templates for international phone number formatting and country code usage, you can bundle them all together in a Recipe.

Using Recipes, you can run all country phone standardization templates together so that you do not have to run them by hand every time you want to format your international phone numbers. 

recipes

Recipes are a collection of templates organized into a sequence you can specify. Review this Recipes article for setup instructions.

recpie

You can also add additional phone number formatting templates to a recipe. For example, you may want include the "Format All Other Countries" template, which allow you to format numbers for countries that do not have specific templates.

Recipe Automation

Recipes can be set up to execute all the included templates automatically.

Click the Review button on the Recipes page. Here, you can schedule the entire Recipe to run on a set schedule.

You'll be taken through a three-step process. In the third-step, you'll be able to set an automation schedule for this Recipe, and all of your included templates will be run in order, on the schedule you specify.

automate insycle recipes

Learn more about Why Data Management Is So Time-Consuming And How Recipes Can Help (blog post).

HubSpot Workflow Automation

HubSpot users can schedule their international phone number Recipes directly into HubSpot Workflows. To learn more, see Add Insycle Recipes to HubSpot Workflows.

workflows

Troubleshooting

Phone Number Formatting In HubSpot

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.

phone-format-1.png

Add the space at the end of the phone number field in HubSpot, then click 'Confirm.'

 

phone-format-2.png

Lastly, make sure to click the Save button.

phone-format-3.png

Frequently Asked Questions

Will this template work with all country codes?

No. This template can serve as a good starting point but will require customization to work for other countries.

Which default templates can I use to format phone numbers?
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

Do I have to filter my data?

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

Related Blog Articles