Excel Challenge 6 : Format Phone Numbers

What You Will Learn

1234567890 is hard to scan. (123) 456-7890 is not. Your contact list has five rows of raw 10-digit numbers in column A. You need to display them in a consistent phone format in column B.

A2:A6 — raw 10-digit numbers like 1234567890
B2:B6 — your formatting formula goes here
B9 — length check on the first number (should be 10)
B10 — count of formatted rows

There are three ways to format the numbers. The one you choose depends on how much control you need:

Method 1: TEXT with a Phone Mask (Simplest)

The pattern "(###) ###-####" tells Excel how to display the digits. The # symbols are placeholders — each one holds a digit. The parentheses, space, and dash are added automatically:

=TEXT(A2, "(###) ###-####")

This is the shortest version, but mask support can vary across spreadsheet tools. When it works, it is the fastest path to the right result.

Method 2: Build from Pieces (Validator Expects This)

This method slices the number into three parts — area code, middle block, and last 4 digits — then joins them with &:

="("&LEFT(A2,3)&") "&MID(A2,4,3)&"-"&RIGHT(A2,4)

For the number 1234567890:

LEFT(A2,3) — takes characters 1–3 → "123"
MID(A2,4,3) — starts at character 4, takes 3 → "456"
RIGHT(A2,4) — takes last 4 characters → "7890"
The & symbol joins everything into "(123) 456-7890"

This is the most explicit approach. Each piece of the number is handled separately, which makes it easy to adjust if the display format ever needs to change.

Method 3: Insert Dashes with REPLACE

If you only need dashes instead of the full parenthesized format, REPLACE can insert separators at fixed positions:

=REPLACE(REPLACE(A2, 4, 0, "-"), 8, 0, "-")

The inner REPLACE inserts a dash before character 4 (after the area code). The outer REPLACE inserts another dash before character 8 (after the middle block). The result is "555-111-2222".

Three ways to format: TEXT mask, piece-by-piece with LEFT/MID/RIGHT, or dash insertion with REPLACE.

The Audit Formulas

B9: =LEN(A2) — confirms the first raw number has 10 digits. If it returns something other than 10, the source data needs cleaning before formatting will work correctly.
B10: =COUNTA(B2:B6) — counts how many formatted results exist. If this number is less than 5, the formula was not filled down to every row.

LEN checks input quality — even the best formatting formula cannot fix missing digits. COUNTA confirms the formula was applied everywhere it should be. Together they act as a simple data quality gate before the formatted column is used.

Formatting improves readability but does not fix bad input. If the source has fewer than 10 digits or contains dashes or spaces, the formatted result will still be wrong. Clean the input first, then format it.