Text
Clean and format 10-digit phone numbers with formulas
Strip spaces, parentheses, hyphens, and dots from imported phone strings, validate 10-digit length, and format uniformly.
Strip formatting punctuation down to raw digits first
CRM imports mix "(415) 555-0198", "206.555.0144", and "312 555 0177" in the same column. Chaining SUBSTITUTE inside LET strips spaces, parentheses, hyphens, and dots to isolate pure digits.
Checking LEN(d) <> 10 immediately flags incomplete 7-digit local numbers before slicing LEFT(d, 3), MID(d, 4, 3), and RIGHT(d, 4).
| RawPhone (A) | Cleaned Digits | Digit Count | Standardized Output |
|---|---|---|---|
| (415) 555-0198 | 4155550198 | 10 | (415) 555-0198 |
| 206.555.0144 | 2065550144 | 10 | (206) 555-0144 |
| 555-0199 | 5550199 | 7 | Check digits |
10-digit sanitizer and formatter
Capturing cleaned digits once as LET variable d keeps the length validation and 3-3-4 slicing readable and fast.
After removing the illustrated punctuation, require exactly ten ASCII digits. This checks the example format only, not whether a number is assigned or reachable. International prefixes, extensions and non-ASCII digits need separate handling.
=LET(d, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, " ", ""), "(", ""), ")", ""), "-", ""), ".", ""), IF(OR(LEN(d)<>10, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(d,"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")<>""), "Check digits", "(" & LEFT(d, 3) & ") " & MID(d, 4, 3) & "-" & RIGHT(d, 4)))Invalid 7-digit number check
Short entry "555-0199" strips to 7 digits and returns "Check digits" rather than mangled slices.
=LET(d, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A4, " ", ""), "(", ""), ")", ""), "-", ""), ".", ""), IF(OR(LEN(d)<>10, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(d,"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")<>""), "Check digits", "(" & LEFT(d, 3) & ") " & MID(d, 4, 3) & "-" & RIGHT(d, 4)))Try the example
Use the original values or change them to test your understanding.