How to format phone numbers in excel

To format phone numbers in Excel, use the Format Cells dialog with custom number formats, apply the TEXT function, or clean data with formulas like SUBSTITUTE and REGEXREPLACE before formatting. The method you choose depends on whether phone numbers are stored as clean numeric values, text strings, or messy data requiring extraction and cleaning.

How to format phone numbers using the Format Cells dialog

To format phone numbers using the Format Cells dialog, select the cells containing phone numbers, press Ctrl+1 to open the dialog, navigate to the Number tab, and select Custom. Enter a custom pattern in the Type field to display phone numbers in your desired format.

This method works best when phone numbers are stored as numeric values without extra characters. The formatting changes only the display without altering the underlying value.

Common custom format codes for phone numbers:

1. (###) ###-#### displays numbers as (555) 555-1234, where # shows digits when present.
2. (000) 000-0000 forces leading zeros for shorter inputs.
3. [<=9999999]###-####;(###) ###-#### handles both 7-digit and 10-digit numbers automatically. This is Microsoft's built-in format code.
4. +1 (###) ###-#### adds a fixed country code before US-style digits.
5. +## ###-#### accommodates variable 2-digit country codes.
6. (###) ###-#### x#### appends an extension section after the main number.

Test format codes with sample data, as behavior varies by input length and Excel version.

How to format phone numbers using the TEXT function

To format phone numbers using the TEXT function, enter a formula that converts numeric values into formatted text strings. The result displays as a phone number but becomes text that cannot be used in numeric calculations.

Syntax:

$=TEXT(value,forma{t}_{t}ext)$

The value parameter is the cell reference or number to format. The format_text parameter is a string in quotes specifying the display format.

Step-by-step process:

1. Enter the unformatted phone number (e.g., 5555551234) in cell A2.
2. Click on the cell where you want the formatted phone number to appear.
3. Enter the formula: =TEXT(A2, "(###) ###-####")
4. Press Enter. The number displays as (555) 555-1234.
5. Use conditional formatting within TEXT to handle variable-length numbers: =TEXT(A2, "[<=9999999]###-####;(###) ###-####")

This conditional format displays 7-digit numbers as 555-1234 and 10-digit numbers as (555) 555-1234.

How to clean phone numbers before formatting

To clean phone numbers containing unwanted characters such as spaces, dashes, parentheses, or text, use functions like SUBSTITUTE, TEXTJOIN, REGEXREPLACE, CLEAN, and TRIM. Cleaning removes inconsistencies and produces a digit-only string ready for formatting.

Using SUBSTITUTE to remove specific characters:

Nest multiple SUBSTITUTE functions to remove several unwanted characters at once:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"-",""),"(",""),")","")
$=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"",""),"-",""),"(",""),")","")$

This formula removes spaces, dashes, and parentheses from the value in A2.

Using REGEXREPLACE to keep only digits (Excel 365+):

=REGEXREPLACE(A2,"[^\d]","")
$REGEXREPLACE(A2,"{[}^d]","")$

$EGEXREPLACE(A2,"{[}^{\text{d}}]","")$

This removes all non-digit characters using a regular expression pattern, leaving only numeric characters.

Using TEXTJOIN with MID and ROW to extract digits:

=TEXTJOIN("",TRUE,IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))

This array formula iterates through each character and keeps only digits. Press Ctrl+Shift+Enter in Excel versions before Excel 365.

Cleaning workflow:

1. Apply a cleaning formula to strip unwanted characters from raw phone number data.
2. The output is a digit-only string or number.
3. Apply formatting using the Format Cells dialog or the TEXT function to display the cleaned number as a formatted phone number.

Custom format code placeholders

Excel uses specific placeholders in custom format codes to control phone number display:

1. # displays a digit when present and shows nothing for absent digits.
2. 0 displays a digit when present and shows zero for absent digits, preserving leading zeros.
3. Text in quotes displays literal characters such as parentheses, spaces, or plus signs.
4. Semicolons separate format codes for different conditions, enabling formats like [<=9999999]###-####;(###) ###-#### to handle multiple number lengths.

These placeholders combine to create formats for domestic numbers, international numbers with country codes, and numbers with extensions.