Spreadsheet Practice

How to format messy phone numbers in Google Sheets

Google Sheets formula

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

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").

Result on the example
one result per row in B2, filled down to B6
Excel
See below

See it work

The formula is already in B2, filled down to B6. Change any input and the result updates, or edit the formula to experiment.

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.

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

Result"(612) 555-0142"

  1. A2"612.555.0142"

    The value in A2.

  2. REGEXREPLACE(A2,"\D","")"6125550142"

    REGEXREPLACE: Replaces every match. Use $1 for a captured group.

    • textA2"612.555.0142"
    • regular_expression"\D"
    • replacement""
  3. REGEXREPLACE(REGEXREPLACE(A2,"\D",""),"^1?(\d{3})(\d{3})(\d{4})$","($1) $2-$3")"(612) 555-0142"

    REGEXREPLACE: Replaces every match. Use $1 for a captured group.

    • textREGEXREPLACE(A2,"\D","")"6125550142"
    • regular_expression"^1?(\d{3})(\d{3})(\d{4})$"
    • replacement"($1) $2-$3"

In 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.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=REGEXREPLACE(A2,"\D","")6125550142, 6515550187, 17635550021…Different resultOnly 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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Format messy phone numbers

In B2, turn the phone number into the format (612) 555-0142, whatever punctuation it was typed with, and fill down to B6.

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.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.