Bulk Reformat UK and US Phone Numbers With 44 & 001 International Dialling Codes

SMS Marketer? Here’s a freebie for you! 🎉

Wrongly formatted phone numbers entered by customers or team members can cause all sorts of issues when trying to process them in CRM and SMS marketing platforms. The most common issues I see are:

• International dialling code not included, such as 44 for the UK or 001 for the US
• Unnecessary symbols such as brackets (), dashes – and even text such as label for who the number belongs to
• Missing leading zeros or unconventional use of the International dialling codes such as 4407123456789

Fed up with trying to correct these manually I decided to solve the issue once and for all and wrote a RegEx formula in Google Sheets to reformat numbers in bulk including the correct country code for the UK 🇬🇧 and US 🇺🇸.

UK RegEx formula:

=IF(A2="", "", 
IF(LEFT(REGEXREPLACE(A2, "[^\d]", ""), 1) = "1", 
"011" & RIGHT(REGEXREPLACE(A2, "[^\d]", ""), LEN(REGEXREPLACE(A2, "[^\d]", "")) - 1), 
IF(LEFT(REGEXREPLACE(A2, "[^\d]", ""), 2) = "+1", 
"011" & RIGHT(REGEXREPLACE(A2, "[^\d]", ""), LEN(REGEXREPLACE(A2, "[^\d]", "")) - 2), 
"011" & REGEXREPLACE(A2, "[^\d]", "")
)
)
)

 

How does it work?

1. First, it checks if there’s anything in A2; if not, it returns an empty string.
2. It then checks if the cleaned number (all non-digits removed) starts with “440”. If it does, it strips the “0” after “44” to correct the format.
3. If the number starts with “0” (after cleaning), it removes this leading “0” and prepends “44”.
4. If the cleaned number starts with “44”, it leaves it as is.
5. For any other scenario, it adds “44” to the start of the cleaned number.

This approach ensures that numbers starting with “440” are correctly formatted by removing the redundant “0” after the country code.

 

US RegEx formula:

=IF(A2="", "", 
IF(LEFT(REGEXREPLACE(A2, "[^\d]", ""), 3) = "001", 
REGEXREPLACE(A2, "[^\d]", ""), 
IF(LEFT(REGEXREPLACE(A2, "[^\d]", ""), 1) = "1", 
"001" & RIGHT(REGEXREPLACE(A2, "[^\d]", ""), LEN(REGEXREPLACE(A2, "[^\d]", "")) - 1), 
IF(LEFT(REGEXREPLACE(A2, "[^\d]", ""), 2) = "+1", 
"001" & RIGHT(REGEXREPLACE(A2, "[^\d]", ""), LEN(REGEXREPLACE(A2, "[^\d]", "")) - 2), 
"001" & REGEXREPLACE(A2, "[^\d]", "")
)
)
)
)

 

How does it work?

1. Empty Check: If A2 is empty, returns an empty string to avoid unnecessary processing.
2. Check for Existing “001” Prefix: If the cleaned number (all non-digits removed) already starts with “001”, it simply cleans the number without adding any prefix.
3. Handle Leading “1”: If the number starts with “1” (after cleaning), removes this “1” and prepends “001”.
4. Handle Leading “+1”: If the number starts with “+1”, it removes “+1” and adds “001” to the start.
5. Default Case: For numbers that do not start with “1”, “+1”, or “001”, it prepends “001”.

This ensures that numbers already starting with “001” are left unchanged, while all others are formatted to start with the “001” prefix, removing any initial “1” or “+1” accordingly.

Alternatively, if you’d like to download a copy of the template as and Excel (.EXSX) file then you can do so by clicking here.

The above RegEx formulas could easily be adapted for other countries too by just changing the “44” dialling code.

Hope this helps!