Format Phone / ID Numbers Without Changing Them

Excel Formulas › Advanced

All versionsFormatting

Display a 10-digit number as a phone number, or an ID as 000-00-0000, using a format code — the cell keeps a plain number you can still sort and match on.

Quick formula: Format Cells → Custom for a 10-digit number:
(000) 000-0000
The 0 placeholders slot each digit into the pattern. The cell’s value stays a number; only the display gets the parentheses and dashes.

Functions used (tap for the full reference guide):

TEXT

The example

A stored number shown as a formatted phone.

AB
1Stored valueDisplays
22145550918(214) 555-0918

The formula

A pattern of 0 placeholders:

(000) 000-0000 → phone 000-00-0000 → ID // digits slotted into the pattern

How it works

Each 0 is a required digit position:

  1. Enter the number as a plain number (no punctuation): 2145550918.
  2. Apply a custom format where 0 marks each digit and literal characters (( ) - space) sit between them.
  3. Excel slots the digits into the pattern from the right; the stored value is unchanged.
  4. Because it’s still a number, leading-zero IDs need enough 0s in the format to pad them (e.g. 00000 for a 5-digit ZIP).

Numbers vs text for IDs. This works only while the value is a true number. If an ID can start with zero or exceed 15 digits (credit cards, some account numbers), store it as text instead — numbers drop leading zeros and lose precision past 15 digits. Format codes are for genuine numbers like phones and ZIPs.

Try it: interactive demo

Live demo

10-digit number → phone format.

Displays:

Variations

SSN-style ID

Three-part code:

000-00-0000

5-digit ZIP

Pad leading zeros:

00000

As text (TEXT)

Bake it into a string:

=TEXT(A1, "(000) 000-0000")

Pitfalls & errors

Only for true numbers. The format applies to numeric cells. A phone stored as text won’t reformat with this — use the TEXT-based phone recipe for text.

15-digit limit. Numbers lose precision beyond 15 digits, so credit cards / long account numbers must be text.

Leading zeros need padding. A number can’t store a leading zero, so use enough 0 placeholders to pad shorter values.

Practice workbook

📊
Download the free Format Phone / ID Numbers Without Changing Them practice workbook
Numbers shown with phone/ID/ZIP format codes (value preserved), the TEXT variant, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I format a number as a phone number in Excel?
Store it as a plain 10-digit number and apply the custom format (000) 000-0000. The digits slot into the pattern while the cell keeps a numeric value.
Why do leading zeros disappear from my IDs?
Numbers can't store leading zeros. Either pad with enough 0 placeholders in the format (like 00000 for a ZIP) or store the ID as text.
Should account numbers be numbers or text?
Text. Numbers lose precision past 15 digits and drop leading zeros, so credit-card and long account numbers belong in text cells.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Standardize phone format · Pad with zeros · Custom number format

Function references: TEXT