Function Problems

How to Format Phone Numbers as 1(XXX)XXX-XXXX in Excel

Kushani Nimanthika Oct 20, 2026 868 views

Question details

The user wants to format a column of phone numbers to consistently display as 1(XXX)XXX-XXXX, overcoming issues where source data contains varying punctuation like dashes or parentheses.

How to Format Phone Numbers as 1(XXX)XXX-XXXX in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Standardizing contact lists where phone numbers have been entered with mixed formats, causing standard Excel number formatting to fail.
Observed behavior: Applying a standard custom number format does not update all values because some cells contain text characters, spaces, or existing punctuation that prevents Excel from recognizing them as pure numbers.

Before you start

Before applying advanced formulas, consider checking your dataset for unnecessary leading or trailing spaces. You can use the TRIM function or the Find and Replace tool to quickly clean up basic spacing issues.

Solution 1

Use Custom Number Formatting for Clean Numeric Data

If your phone numbers already consist of exactly 10 pure numeric digits with no dashes or parentheses, applying a custom number format is the fastest method.

Custom number formatting only changes how the data is displayed, not the underlying value. It requires the source cell to contain purely numeric values to work correctly.

Select the Data Range

Highlight the column or cells containing your 10-digit phone numbers.

Open Format Cells

Right-click the selected area and choose 'Format Cells', or press Ctrl+1 on your keyboard.

Apply Custom Format

In the Number tab, select 'Custom' from the Category list. In the 'Type' box, enter 1(000)000-0000 and click OK.

Solution 2Recommended

Apply an Array Formula to Clean and Format Mixed Data

When your column contains a messy mix of formats, dashes, or parentheses, use this advanced formula to extract only the numeric characters and automatically format them.

Format Contacts Easily

Clean and Format Phone Numbers Seamlessly with WPS Spreadsheet

Handle messy data and format large contact lists effortlessly using WPS Spreadsheet. It supports all advanced Excel functions including TEXTJOIN and custom cell formatting, with a clean and familiar interface.

1. Open Your Data: Launch WPS Spreadsheet and open your contact list workbook.
2. Apply Custom Formats: Select your column, press Ctrl+1, choose Custom, and input 1(000)000-0000.
3. Use Advanced Formulas: For messy text, simply paste the TEXTJOIN array formula provided in the solution to extract and format perfectly.

Free to download with a lightweight installation100% compatible with Microsoft Excel files (.xlsx)Supports advanced array formulas for data extractionBuilt-in robust data cleaning and formatting tools

microsoft office alternative - wps office

Frequently Asked Questions

Why does the custom number format ignore some of my cells?

Custom number formats only alter the display of purely numeric values. If a cell contains any text characters, spaces, hyphens, or parentheses, Excel treats the entire cell as text and ignores the custom number format. You must remove those characters first.

How can I quickly remove all dashes and parentheses without a formula?

You can use the Find and Replace feature. Press Ctrl+H to open the dialog. In the 'Find what' box, type a hyphen (-) and leave 'Replace with' completely blank, then click 'Replace All'. Repeat this process for opening '(' and closing ')' parentheses. Once cleaned, you can apply your custom format.

What if the TEXTJOIN formula returns an error?

A #NAME? error typically means your version of Excel does not support the TEXTJOIN function (introduced in Excel 2019). A #VALUE! error usually indicates that you need to confirm the formula as an array by pressing Ctrl+Shift+Enter instead of just Enter.

Excel & Data Problems