Skip to main content
Submit or View Requests
Deutsch Español (España) Français (France) Italiano (Italia)

Using Google Sheets to format numbers

When using one of Aircall's integrations, it is important that your contact phone numbers are formatted correctly. Many CRMs and help desks allow you to export and import contacts, so Google Sheets can be a great tool for mass-updating number formats.

By exporting your contacts, reformatting each number in Google Sheets, and then re-uploading the file, you can quickly make sure your integrations work to their full potential.

Aircall supports several number formats, but we recommend using E.164 format for all integrations.

NOTE:
E.164 is an international standard format that uses:
• A leading +
• Country code
• Subscriber number
With no spaces or special characters.
Example: +15555555555

For specific information about number formats, please see What number format you can use to log calls.

Helpful Google Sheets functions

Two useful Google Sheets functions for formatting numbers into E.164 are:

  • REGEXREPLACE

  • REPLACE

You will often use them together:

  1. Use REGEXREPLACE to remove unwanted characters.

  2. Use REPLACE to remove local prefixes such as 0 or 011.

  3. Add the correct + and country code.

Using REGEXREPLACE to clean numbers

When using REGEXREPLACE, you need to specify:

  • The cell to format

  • The characters to replace

  • What you want to replace them with

In this example:

  • You format the cells in column A (starting with A1)

  • You replace all non-digit characters ('D')

  • You replace those characters with nothing (''), which removes them

You also prepend the correct country code. For US numbers, you can use:

='+1'&REGEXREPLACE(A1, 'D', '')

Enter this in cell B1, then drag the formula down for the remaining cells. This:

  • Cleans out non-digit characters

  • Adds +1 in front of the number

You now have US numbers formatted in E.164 in column B.

Using REPLACE to remove prefixes

When using REPLACE, you need to specify:

  • The cell to format

  • The position and length of the characters to replace

  • The replacement value

Example 1: Remove a leading 0

In this example:

  • You format the cell in column A (A1)

  • You replace only the first character

  • You replace that character with nothing

This removes the leading 0 from the number:

=REPLACE(A1, 1, 1, "")

Example 2: Remove an international prefix

In this next example:

  • You format the cell in column A (A2)

  • You replace the first four characters

  • You replace those characters with nothing

This removes 011 and the following space:

=REPLACE(A2, 1, 4, "")

In both examples, the number has been cleaned but is still not in E.164 format. You now need to:

  • Add the correct + and country code

  • Remove any remaining non-digit characters

Combining REPLACE and REGEXREPLACE

After using REPLACE, you can use REGEXREPLACE to clean up remaining characters and add the correct prefix.

Example 1: Add +33 and remove spaces

For a French number in B1, you can use:

='+33'&REGEXREPLACE(B1, 'D', '')

This:

  • Adds +33 at the beginning

  • Removes spaces and other non-digit characters

Example 2: Add + and clean the number

For another number in B2, you can use:

='+' & REGEXREPLACE(B2, 'D', '')

This:

  • Adds + at the beginning

  • Removes spaces and other non-digit characters

By combining REPLACE and REGEXREPLACE, you can convert many different local formats into E.164.

TIP:
Use separate sheets for each country code to make formatting easier and to avoid mixing different country formats in the same column.

 

Jordan W.