categories.Text & CleanupAdvanced

Clean phone numbers with nested SUBSTITUTE and flag short ones with LEN

SUBSTITUTE erases one punctuation character at a time, so a number carrying two kinds of separators needs two nested calls to come out clean — and once they're gone, LEN counting what's left is the simplest way to catch one that was never typed in full.

Task:

You're prepping a batch of customer phone numbers for a dialer system, and the CRM export has them with a space after the area code and a dash before the last four digits. The dialer only accepts bare digits, so in column C strip both characters out with SUBSTITUTE to get a clean, digits-only number. Then in column D, use LEN together with IF to flag whether each cleaned number came out to a valid 10 digits — "Yes" if it did, "No" if it didn't. Fill in rows 2 through 5.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

5 rows × 4 columns8 cells you fill in
ABCD
1CustomerPhone (original)Phone (clean)Valid
2Lena Ortiz415 555-0132
3Derek Finch212 555-0187
4Priya Shah650 555-016
5Marcus Webb305 555-0121
What this exercise teachesMay contain the answer

SUBSTITUTE(B2," ","") removes the space right after the area code, and nesting that call inside a second SUBSTITUTE(…,"-","") then strips the dash out of whatever the inner call already returned — the two run in sequence, each one clearing out a single character the other leaves untouched, until nothing but bare digits is left. LEN(C2)=10 doesn't check whether those digits form a real phone number, only how many of them survived the cleanup, which is exactly why it catches Priya Shah's row: her cleaned number comes out to 650555016, nine digits rather than ten, because the last digit was never typed in the original export — not because of anything to do with spaces or dashes. IF just turns that count into the "Yes" or "No" the dialer import is checked against.