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 

  1. Filter records 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 templates for each country and add to a recipe to run automatically.

 

Step-by-Step Instructions

1. Filter Records Down to a Single Country

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. 

country1.png

Click Search and scroll down to the Record Viewer at the bottom of the screen. You will see all records that match your filter.

2. Set the Changes to Make on the Country Code and Phone Number

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.

country2.png

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.

country3.png

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. 

country4.png

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:

country5.png

3. Preview Changes and Update CRM Records

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.

country6.png

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.

country7.png

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.

country8.png

Create Templates for All Countries and Automate

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.

country9.png

Learn more:

Advanced How Tos

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.

country10.png

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.

country11.png

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

 

country12.png

Lastly, make sure to click the Save button.

country13.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 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