Clean phone numbers

Intermediate

When you import contact data into a CRM, phone numbers often come in inconsistent formats like (415) 555-0132, 415-555-0132, or 415 555 0132. A quick way to standardize them is to remove the extra characters and keep only digits.

In this sheet, column B contains raw phone numbers. Your job is to create a cleaned version in column C using nested SUBSTITUTE.

What you need to do:

In cell C2, write a formula that starts with B2 and removes these characters: ( ) - and spaces
Fill the formula down through C13
Each cleaned phone number should end up as a 10-digit string

Tip: SUBSTITUTE replaces one piece of text at a time, so you’ll need to nest it (use the result of one SUBSTITUTE as the input to the next).

Need some help?

Hint 1

Start by using SUBSTITUTE on B2 to remove one character (for example, remove the opening parenthesis).

Hint 2

To remove multiple different characters, wrap your first SUBSTITUTE inside another SUBSTITUTE, and repeat until all characters are removed.

Hint 3

If your result still has separators, check that you also removed spaces, not just parentheses and dashes.

Related function(s)

SUBSTITUTE TRIM CLEAN TEXT VALUE

More exercises

Clean and standardize namesAdvanced

Complex text parsingAdvanced

Parse full addressIntermediate

Extract domain from emailIntermediate

Back to exercises overview

Exercise

Clean phone numbers

Intermediate

In this sheet, column B contains raw phone numbers. Your job is to create a cleaned version in column C using nested SUBSTITUTE.

What you need to do:

In cell C2, write a formula that starts with B2 and removes these characters: ( ) - and spaces
Fill the formula down through C13
Each cleaned phone number should end up as a 10-digit string

Tip: SUBSTITUTE replaces one piece of text at a time, so you’ll need to nest it (use the result of one SUBSTITUTE as the input to the next).