Excel Challenge 7 : Format Phone Numbers

The Problem

Raw phone numbers are technically usable, but they are not pleasant to scan. A block like 1234567890 takes more effort to read than a number broken into the familiar phone pattern.

That readability matters in contact lists, call sheets, CRM exports, and support queues where people need to recognize numbers quickly. Formatting does not magically fix bad data, but it does make clean 10-digit inputs easier to review and use.

The flow below shows the cleanup idea. The raw digits stay as the source, the formula creates a readable display, and the audit cells check whether the list looks complete enough to trust.

The Problem: Raw Digits Need a Readable Phone Pattern Formatting makes clean numbers easier to scan while the audit checks the source shape.

In this workbook, each customer phone value starts as plain digits. The challenge is to create the professional phone display beside each row, then complete the audit checks for source length and formatted-record count.

The raw digits should remain available as the original source.
The formatted column should use one consistent display pattern.
The audit section should confirm the first source length and formatted count.

That gives the sheet both readability and a small quality check. The user can see the formatted numbers, but still has a quick reminder that formatting depends on clean input.

How We Solve It

The most dependable route for this challenge is to build the phone display from pieces. The formula pulls the area code, middle block, and final four digits, then joins them with parentheses, a space, and a dash.

Method 1: TEXT with a phone mask

Method 1: Apply one custom mask to the full 10-digit number.

This is the shortest version when your spreadsheet engine supports custom phone masks inside TEXT. The pattern tells Excel where the parentheses, space, and dash should go, so every 10-digit value is displayed in the same readable style.

This solves the presentation problem quickly, but mask support can vary. That is why the challenge solution uses the more explicit text-slicing method shown next.

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

Method 2: Build the format manually

Method 2: Build the format piece by piece when you want more control.

This is the method used in the solution. It pulls out the area code, middle block, and last four digits separately, then joins them back together with text symbols.

This solves the formatting problem in a very explicit way. It is useful when the layout might change later, because each part of the number can be adjusted separately before the final string is joined together.

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

Method 3: Insert separators with REPLACE

Method 3: Insert separators into fixed positions with REPLACE.

This method works well when the goal is just to add separators into a fixed-length string. It is another useful cleanup pattern, even though the challenge itself expects the parenthesized phone format.

This solves a simpler presentation problem where the sheet only needs readable separators, not a full phone mask. It is a good option when the source data is clean and the main goal is to break the number into more readable chunks.

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

Function Explanation

1. TEXT

TEXT formats a value according to a pattern you provide. In phone-formatting tasks, that pattern can turn a 10-digit number into a standard phone display.

That makes it a good fit when the input is already clean and your spreadsheet engine supports the mask you need. When mask behavior is inconsistent, slicing with LEFT, MID, and RIGHT is safer.

Learn more this functionTEXT

2. LEN

LEN counts characters in the raw input. We use it in the summary to confirm the first value has 10 digits before formatting.

That is a light audit rather than a full validation system. It reminds the user that the phone mask assumes the source value has the expected length.

Learn more this functionLEN

3. COUNTA

COUNTA counts non-empty cells. Here it tells us how many formatted results exist in column B.

That summary check helps confirm the formatting formula was filled down through the full visible list instead of stopping early.

Learn more this functionCOUNTA

Formatting formulas improve readability, but they do not fix bad input by themselves. If the source data has missing digits, the result can still look wrong, so validation and formatting often go together.