Format Phone Numbers for Country Codes

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:

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:

• Automation and Manual Operations
• HubSpot's Workflows
• Why Data Management Is So Time-Consuming And How Recipes Can Help

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.

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

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.