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.
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.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Phone (original) | Phone (clean) | Valid |
| 2 | Lena Ortiz | 415 555-0132 | ||
| 3 | Derek Finch | 212 555-0187 | ||
| 4 | Priya Shah | 650 555-016 | ||
| 5 | Marcus Webb | 305 555-0121 |
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.