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.

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's phone number formatting features can conflict with Insycle's ability to transform them. If you have HubSpot's automatic phone number formatting 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, clicking "Remove number formatting" 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. 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 down into small segments and use multiple templates rather than running operations for your entire database.