How to Format Phone Numbers in Excel: Easy Guide

Sharing is caring!

Formatting phone numbers correctly helps keep data clean, readable, and consistent. When phone numbers are stored as plain numbers, they often lose important details like leading zeros, country codes, or spacing.

Using Microsoft Excel, you can format phone numbers in several simple ways, depending on your data type and use case.

In this guide, we will explain multiple methods to format phone numbers using custom formats, formulas, and built-in tools, with clear examples that work for beginners and advanced users.

Common Phone Number Formats Used in Excel

Different regions follow different phone number patterns. Below are common formats used in spreadsheets.

Choosing the right format depends on location, audience, and data usage.

Format Phone Numbers Using Custom Number Format

The Custom Number Format option is the fastest way to format fixed-length phone numbers.

Steps to Apply Custom Format

1. Select the cells containing phone numbers
2. Press Ctrl + 1
3. Click Number → Custom
4. Enter a format pattern
5. Click OK

Common Custom Format Codes

Important: This method works only if the phone number has exactly 10 digits.

Format Phone Numbers Using TEXT Function

The TEXT function is useful when you need formatted phone numbers as text output, especially for reports or exports.

Syntax

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

Example

If cell A1 contains 9876543210, the formula returns:

(987) 654-3210

This approach is helpful when:

• Combining phone numbers with other text
• Exporting data to external systems
• Preventing Excel from changing formatting

Format Phone Numbers Using CONCAT and MID Functions

When phone numbers are stored as plain text, formulas offer more control.

Example Formula

="("&MID(A1,1,3)&") "&MID(A1,4,3)&"-"&MID(A1,7,4)

When to Use This Method

• Numbers include country codes
• Data comes from imports or CSV files
• Phone length varies by row

This method works well with messy datasets.

Add Country Code to Phone Numbers

If your data needs international formatting, adding a country code is important.

Example: Add +1 to US Numbers

="+1 "&TEXT(A1,"(###) ###-####")

Example: Add +44 for UK Numbers

="+44 "&LEFT(A1,2)&" "&MID(A1,3,4)&" "&RIGHT(A1,4)

This keeps numbers readable while maintaining global compatibility.

Prevent Excel from Removing Leading Zeros

Excel automatically removes leading zeros from numeric values. To avoid this:

Method 1: Format Cells as Text

• Select cells
• Right-click → Format Cells
• Choose Text
• Enter phone numbers again

Method 2: Add Apostrophe

'0123456789

This forces Excel to treat the value as text, preserving formatting.

Convert Unformatted Numbers into Phone Numbers

Unformatted phone numbers often come from imports, online forms, CRM exports, or copied web data. These numbers may include spaces, symbols, or inconsistent layouts. Excel formulas help convert them into a clean and readable phone number format.

Example 1: Remove Dashes Before Formatting

Raw value in A1

987-654-3210

Step 1: Remove dashes

=SUBSTITUTE(A1,"-","")

Step 2: Apply phone format

=TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####")

Result

(987) 654-3210

This method is useful when numbers contain mixed separators.

Example 2: Remove Spaces from Phone Numbers

Raw value in A1

987 654 3210

Formula

=TEXT(SUBSTITUTE(A1," ",""),"(###) ###-####")

Result

(987) 654-3210

This helps clean phone numbers copied from emails or PDFs.

Format Phone Numbers with Extensions

Extensions are common in office phone systems.

Example Formula

=TEXT(A1,"(###) ###-####")&" x"&B1

Result

(123) 456-7890 x204

This keeps the main number and extension clear and readable.

Using Flash Fill to Format Phone Numbers

Flash Fill automatically detects patterns and formats data.

1. Type formatted phone number manually in adjacent column
2. Start typing the next one
3. Press Ctrl + E

Flash Fill works best when data is consistent and clean.

Best Practices for Phone Number Formatting in Excel

• Store phone numbers as text or formatted values
• Avoid using raw numeric values for phone data
• Keep formats consistent across the sheet
• Use formulas when handling mixed or imported data
• Add country codes for international datasets

Following these practices helps maintain data integrity and avoids formatting errors.

Final Thoughts

Formatting phone numbers in Excel does not require advanced skills. Using custom formats, formulas, and built-in tools, you can quickly turn raw data into professional, readable phone numbers.

Whether you manage contact lists, business leads, or customer records, proper phone number formatting improves clarity, accuracy, and usability across your spreadsheets.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.