# How to format messy phone numbers in Google Sheets

Strip everything but digits, then rebuild the format with capture groups: =REGEXREPLACE(REGEXREPLACE(A2,"\D",""),"^1?(\d{3})(\d{3})(\d{4})$","($1) $2-$3").

**Google Sheets:** `=REGEXREPLACE(REGEXREPLACE(A2,"\D",""),"^1?(\d{3})(\d{3})(\d{4})$","($1) $2-$3")`

**Excel:** Excel 365 has REGEXREPLACE, and the same formula works there. In older versions, remove the punctuation with SUBSTITUTE and rebuild the number with LEFT, MID and RIGHT.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Phone (as typed) | Formatted |
| 612.555.0142 |  |
| (651) 555 0187 |  |
| +1 763-555-0021 |  |
| 9525550199 |  |
| 1 (218) 555-0110 |  |

Formula in B2, filled to B6: `=REGEXREPLACE(REGEXREPLACE(A2,"\D",""),"^1?(\d{3})(\d{3})(\d{4})$","($1) $2-$3")`

Result: (612) 555-0142; (651) 555-0187; (763) 555-0021; (952) 555-0199; (218) 555-0110

## How it works

The inner REGEXREPLACE deletes every non-digit (`\D`), leaving 10 or 11 digits. The outer one matches an optional leading 1 and then three groups of 3, 3 and 4 digits, and writes them back as `($1) $2-$3`, where $1 to $3 are the groups.

## Other ways

- `=REGEXREPLACE(A2,"\D","")`: Only the digits, for systems that want an unformatted number. ("6125550142", and the leading 1 is kept where there is one.)

## Common mistakes

- Numbers that don't have 10 digits (or 11 starting with 1) don't match the second pattern, so they come back as bare digits. Filter for those to fix by hand.
- Extensions like "x204" add digits and stop the match. Split them off first.

## Questions

**How do I use dashes only, like 612-555-0142?**

Change the replacement: `"$1-$2-$3"`.

**How do I add +1 for international format?**

Use `"+1 $1 $2 $3"` as the replacement.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/format-messy-phone-numbers-in-google-sheets