Solved: Using REGEXREPLACE to format 10-digit strings into international phone format while ignoring existing non-numeric characters in Google Sheets

📌 The Problem Explained

In many professional environments, contact lists are compiled from various sources, leading to inconsistent phone number formatting. You might have a column containing values like 1234567890, (123) 456-7890, or 123.456.7890.

To use these for CRM uploads or international dialing, you need them in a standardized format (e.g., +1 (XXX) XXX-XXXX). The challenge is that standard formatting tools often break if the cell already contains dashes or parentheses. You need a solution that first “cleans” the data and then “re-masks” it into the correct international structure.

💡 The Quick Solution

Paste the following formula into an empty cell (assuming your raw data starts in cell A2):

=REGEXREPLACE(REGEXREPLACE(A2, "\D", ""), "(\d{3})(\d{3})(\d{4})", "+1 ($1) $2-$3")

🛠️ Step-by-Step Breakdown

This solution uses “nesting”—one REGEXREPLACE function inside another—to handle the cleaning and formatting in a single step.

Step	Component	Logic
1. Clean	REGEXREPLACE(A2, "\D", "")	This inner function looks for \D (any character that is NOT a digit) and replaces it with an empty string "". This leaves you with a pure 10-digit string.
2. Group	(\d{3})(\d{3})(\d{4})	This part of the outer function identifies three groups of digits: the first 3 (area code), the next 3 (prefix), and the last 4 (line number).
3. Format	"+1 ($1) $2-$3"	This defines the output. $1, $2, and $3 refer back to the groups identified in Step 2, wrapping them in the international prefix, parentheses, and dashes.

How to apply it to your sheet:

1. Open your spreadsheet and click on the cell where you want the formatted number to appear.
2. Type or paste the formula provided above.
3. Hover over the bottom-right corner of the cell until the cursor becomes a plus sign (+).
4. Click and drag down to apply the formula to the rest of your column.
5. (Optional) To keep only the values, highlight the new column, go to Edit > Copy, then Edit > Paste special > Values only.

⚠️ Common Mistakes to Avoid

• The “Text vs. Number” Trap: REGEXREPLACE always outputs a string (text). If you try to perform mathematical operations on the result, it will not work. However, for phone numbers, “Text” format is the preferred standard.
• Assuming 10 Digits: This specific formula is designed for North American 10-digit numbers. If your data includes international numbers with different lengths (e.g., 11 or 12 digits), the regex pattern (\d{3})(\d{3})(\d{4}) will fail to match. Always verify your data length before bulk-applying.