When using one of Aircall's integrations, it is important to have the correct number format for your contacts.
Many CRMs and Helpdesks allow you to export and import contacts, which means Google Sheets can be a great tool for mass updates to the number formats being used. By exporting your contacts, reformatting each number using Google Sheets, and then re-uploading, you are often able to quickly make changes to ensure our integrations are working to their fullest potential.
While several number formats may be supported, we recommend making use of the E.164 format for all integrations.
Two useful functions for formatting numbers to E.164 will be REGEXREPLACE and REPLACE.
REGEXREPLACE:
When using REGEXREPLACE, you will need to enter the cell you are looking to format, the characters you are looking to replace, and the character you would like to replace them with.
In this example, we are choosing to format the cells in column A ( A1 ), we are choosing to replace all non-digit characters ( 'D' ), and are choosing to replace those non-digit characters with nothing ( '' ), which in turn has them completely removed.
You will also notice that we have started our function with '+1'& so that we can add the appropriate country code to the beginning of the number. By entering ='+1'®EXREPLACE(A1, 'D', '') into cell B1, then dragging down for the remainder of the cells, we have been able to successfully format each of these US numbers into E.164 format.
REPLACE:
When using REPLACE, you will need to enter the cell you are looking to format, the range of characters you wish to replace, and the characters you would like to replace that range with.
In this example, we are choosing to format the cell in column A ( A1 ), we are choosing to replace only the first character ( 1,1 ), and are choosing to replace that character with nothing ( '' ). By doing this, we are able to successfully remove the 0 from the beginning of the number.
In this next example, we are choosing to format the cell in column A ( A2 ), we are choosing to replace the first four characters ( 1,4 ), and are choosing to replace those characters with nothing ( '' ). By doing this, we are able to successfully remove the 011 and space from the beginning of the number.
In both of these examples, the number has been updated but is still not in E.164 format. In order to complete the formatting, we will need to make use of REGEXREPLACE again, and add the '+' and country code as needed.
In the first example, we need to add '+33'®EXREPLACE(B1, 'D', '') to insert the '+' and country code to the beginning, as well as remove the spaces in the number.
In the second example, we need to add '+'®EXREPLACE(B2, 'D', '') to insert the '+' to the beginning, as well as remove the spaces in the number.
Making use of these two functions alone, you will be able to reformat many different numbers to the E.164 format.
💡 TIP: Use separate sheets for each country code to make formatting easier!