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 Data Management > Transform Data, and in the top menu select the database and record type. Then explore the templates for an existing solution that may be close to what you need; there are several specific to formatting contact 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 with country codes.

Functions can be layered to make changes in a series of steps. They will cumulatively apply to the field value, executing in order from top to bottom.

The example above uses the Phone Number field, going through a series of cleanup steps, followed by formatting:

1. The Prefix: Remove function will remove the local prefix "0" from the beginning of the values.

2. The Remove: Non-Digits function removes letters, symbols, punctuation, parentheses, dashes, etc., and can give you a fresh start for your phone number data without losing anything important.

   If you plan on inserting new non-digits, such as adding a "+" before the country code or adding a dash as a separator, the Remove: Non-digits function should appear first so it doesn't remove what you've added.

3. The Prefix: Add function will insert the country code, "+61" at the beginning of the phone number. Since these are run in the order they are shown, it's important that Prefix: Remove happens first, making space for the country code.

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

   In this example, we've entered "3,1,4" in the Existing Text field, and a, " " blank space 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 space after 3 characters, another after 1, and another after 4 more. Any remaining numbers will appear after the final separator. 

   You can use other separators such as a dash or period.

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

   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

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 (you could do All, but if you have a large number of records, you may just want to do a chunk for your preview), 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 row. If the results don't look the way you expected, go back to your filters in Step 1 and functions in Step 2 and try making some adjustments, then preview 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.

Under Step 3, click the Review button again, and this time select Update mode.

On the When tab, you should use Run Now the first time you apply these changes to the CRM. If you have a large number of records, you may first want to do a smaller batch to review the results in your CRM.

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.

Return to the Template menu at the top of the page and click Copy to save your configurations as a new version of the template you started with. Then click the pencil to edit your new template name.

Under Step 3, click the Review button, and select Update mode.

On the Notify tab, select the send option appropriate for your automation: Always send, Send when errors, or Do not email. Add any additional recipients who should receive the CSV.

On the When tab, select Automate, and configure the frequency you'd like the template to run. When finished, click Schedule.

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. Additionally, HubSpot users can integrate Insycle Recipes into HubSpot Workflows.

This configuration standardizes UK phone numbers for contacts with UK email addresses. It converts various UK phone number formats into a consistent international format with the +44 country code and proper spacing.

For example:

• 07123456789 → +44 7123 456789 
• 02012345678 → +44 2012 345678
• 44123456890 → +44 1234 567890

In Step 1 of the Transform Data module, use the filter to find UK phone numbers that need cleanup. Look at the phone number field for values that contain "07," "02," "44," or "440." Use the pipe character "|" as an OR separator between values: "07|02|44|440."

You could also refine your results by searching for the "uk" top-level domain in email addresses or web URLs, or by looking at the country field.

In Step 2, set up a rule to remove non-numeric characters and incorrect prefixes, add the "+44" prefix, and then format the phone number to follow the standard regional format.

• Field: Phone Number
• Function 1: Remove: Non-digits
• Function 2: Prefix: Remove, enter the Parameter: "0|44|440"
• Function 3: Prefix: Add, enter the Parameter: "+44"
• Function 4: Format: Phone custom separator 
  • Enter "3,4" in the Existing Text field to indicate the placement of the separator, where each number in the comma-separated list represents how many digits should appear in each group before the next separator is added.
  • Insert a space in the New Text field to use a space as the separator between the digits. 

For example, an original value of "07700900123" would become "+44 7700 900123."

You can run a Preview first to verify the transformation turns out as expected.

Entering extensions in the phone number may prevent the CRM from dialing contacts successfully via click-to-call or when integrating with phone systems that only expect the main number. You can automate a template to move extension values to a designated field.

Before setting things up in Insycle, you might first need to add a custom field for "Extension" in your CRM if it doesn't already exist.

Then, in Step 1 of Insycle's Transform Data module, use the filter to find phone numbers with extensions by looking for values that contain "x," "ext," or "extension." Use the pipe character "|" as an OR separator between values: "x|ext|extension."

In Step 2, set up a rule to move the extension value out of the phone number field to the extension field.

• Field: Phone Number
• Function 1: Extract: Regex move, enter the Parameter: "[a-zA-Z]{1,}.*" then select the Target Field
• Function 2: Remove: Non-digits (to remove the "x," "ext," or "extension" text)

You can run a Preview first to verify the operation turns out as expected.

HubSpot users can use the following template for contact records: Extract Phone Extension into Custom Field (make sure to pick a target field)

Insycle can export the segment of data that resulted from your filters in Step 1. You can export your data one time, immediately, or set up automated exports.

Under Step 1, click the Export button.

In the Export popup, select New File and click Next.

On the Email tab, enter additional email addresses (hit Enter after each), a Subject, and Description. The CSV export will be attached to this email whenever it is generated.

To export the data once:

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.

To set up automation for recurring exports:

On the When tab under Automate, set up the frequency your CSV should automatically be exported--hourly, daily, weekly, or monthly.

Then, data that matches your set filter will automatically be exported and emailed to you or your team on a set schedule, improving data collaboration so you can keep an eye on the most important customer data.

HubSpot has two distinct phone number features that affect how Insycle interacts with phone number data: phone number validation and dynamic phone number formatting. Understanding how these work will help you configure Insycle correctly and avoid formatting issues.

Phone Number Validation Setting

HubSpot's phone number validation can affect how Insycle can format phone numbers. When this setting is enabled, the HubSpot API only accepts phone numbers in the international standard E.164 format. For example: +48879878765 — a plus sign, country code, and digits only, with no spaces or separators. Other phone number formatting options from Insycle will result in errors.

If you're seeing errors like:

• Underlying error message from HubSpot: Property values were not valid: [{"isValid":false,"message":"Number must match format '+18884827768' or '+18884827768 ext 123'.","error":"INVALID_PHONE_NUMBER","name":"validated_phone_number"}]
• Underlying error message from HubSpot: Property values were not valid: [{"isValid":false,"message":"Enter a valid country code.","error":"INVALID_PHONE_NUMBER","name":"validated_phone_number"}]

You have two options to resolve this:

1. Disable phone number validation in HubSpot — With validation turned off, HubSpot will accept phone number formats from Insycle as before.
2. Keep validation enabled and use E.164 formatting — If you prefer to keep HubSpot's validation on, configure Insycle to format phone numbers exclusively in E.164 format.

You can find more details in HubSpot's Set up phone number property validation article.

Dynamic Phone Number Formatting

In HubSpot, fields that use the "number formatting" feature are dynamically displayed in the regional format. In Insycle, the data appears in the plain E.164 format, "+xxxxxxxxxx."

You can format all your phone numbers using the E.164 standard in Insycle. HubSpot will then automatically add ‘number formatting’ and display the phone number in the region-specific format (the format appropriate for the region the phone number is from).

Insycle offers the following built-in templates to help you start formatting phone numbers:

No. These templates can serve as a good starting point, but will require customization for most 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 into smaller segments and use multiple templates rather than running operations across your entire database.