How to Format Phone Numbers in Excel

To format phone numbers in Excel, apply a custom number format such as (000) 000-0000 to cells that hold plain 10-digit numbers. If the phone numbers came in as text, with dots, dashes or spaces, clean them with SUBSTITUTE first and rebuild them with the TEXT function. This guide is for anyone tidying a contact list, CRM export or sign-up sheet, and it covers both cases plus leading zeros and country codes.

Number or text? Check this first#

Before you format phone numbers in Excel, find out what kind of value is in the cell, because the method depends on it:

• A number, such as 2025550147, can be formatted with a custom number format. The cell keeps the number; only the display changes.
• Text, such as 202.555.0147 or (202) 555-0147, ignores number formats completely. You need formulas to clean and rebuild it.

Click a cell and look at it. Numbers line up on the right of the cell by default and text on the left. For a firm answer, type =ISNUMBER(A2) in a spare cell: TRUE means a number, FALSE means text. The guide to numbers stored as text explains why imports often produce text.

All examples use these fictional numbers (the 555-01xx range is reserved for examples):

Format phone numbers with a custom number format#

When the cells hold plain numbers, like A5, a custom format adds the brackets and dash for you:

1. Select the cells.
2. Press Ctrl+1 (Mac: ⌘1) to open Format Cells.
3. On the Number tab, choose Custom.
4. In the Type box, enter (000) 000-0000 and click OK.

The number 6175550190 now shows as (617) 555-0190. Each 0 is a digit placeholder: it shows a digit, or a 0 if there aren't enough digits. The brackets, space and dash are shown as typed. Some other useful formats:

Excel also has a ready-made option: in Format Cells, choose Special > Phone Number (shown for some locales, such as English (United States)). It shows 7-digit numbers as ###-#### and 10-digit numbers as (###) ###-####.

Because the value is still a number, sorting and lookups keep working. The guide to custom number formats in Excel covers the codes in more depth.

Clean phone numbers stored as text with SUBSTITUTE#

Text phone numbers need the extra characters removed before you can format them. SUBSTITUTE replaces one piece of text with another, and replacing a character with "" (empty text) deletes it:

=SUBSTITUTE(A2,".","")

For A2 (202.555.0147) the result is 2025550147, still as text. When a number has several kinds of characters, nest one SUBSTITUTE inside another, one for each character:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"(",""),")",""),"-","")," ","")

For A3, (312) 555-0182, Excel removes the open bracket, then the close bracket, then the dash, then the space, giving 3125550182. Read the formula from the inside out. The SUBSTITUTE function guide and the guide to removing characters in Excel have more patterns, and the cleaning text lesson lets you practise them.

For a one-off clean-up, Find and Replace (Windows: Ctrl+H; Mac: ⌃H) does the same job: find -, leave Replace with empty, click Replace All, then repeat for each character.

Rebuild the phone number with the TEXT function#

Once you have only digits, the TEXT function applies a number format inside a formula and returns text. It needs a number, so put -- (two minus signs) in front of the cleaned text to convert it:

=TEXT(--SUBSTITUTE(A2,".",""),"(000) 000-0000")

The result for A2 is (202) 555-0147. The -- turns the text "2025550147" into the number 2025550147. VALUE does the same thing if you find it easier to read: =TEXT(VALUE(SUBSTITUTE(A2,".","")),"(000) 000-0000").

TEXT gives you a new column of consistently formatted phone numbers that you can copy into another system. Because the result is text, it looks the same everywhere, even in a CSV file. The TEXT function guide and the guide to TEXT function format codes list other codes. The TEXT and VALUE lesson practises both functions together.

Remove a country code before formatting#

Some lists mix 10-digit numbers with ones that include the US country code 1, such as A4: 1-415-555-0123. After removing the dashes you get 11 digits. RIGHT keeps only the last 10:

=TEXT(--RIGHT(SUBSTITUTE(A4,"-",""),10),"(000) 000-0000")

The result is (415) 555-0123. RIGHT(text, 10) returns the last 10 characters, which drops the leading 1. To check that every cleaned number has the right length, use LEN: =LEN(SUBSTITUTE(A2,".","")) should return 10. Anything else needs a closer look.

Format phone numbers with LEFT, MID and RIGHT#

If you prefer to see each part, you can cut the digits into pieces and join them with &. With 10 digits of text in C2 (for example, the cleaned 2025550147):

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

• LEFT(C2,3) is the area code: 202.
• MID(C2,4,3) takes 3 characters starting at the 4th: 555.
• RIGHT(C2,4) is the last four digits: 0147.

The result is (202) 555-0147. This works on text directly, with no conversion, and is easy to adapt for other layouts. The LEFT, RIGHT, MID and LEN lesson covers these functions step by step.

Phone numbers with leading zeros or a plus sign#

Many countries write phone numbers with a leading 0, such as the UK mobile 07700 900123. If you type it as a number, Excel drops the 0 and shows 7700900123. You have three options:

1. Store it as text. Format the column as Text before typing, or start the entry with an apostrophe: '07700 900123. The apostrophe doesn't show in the cell.
2. Use a custom format with enough zeros. 00000 000000 shows the number 7700900123 as 07700 900123, because each 0 placeholder forces a digit.
3. Store international numbers with + as text. +44 7700 900123 can't be a number, so keep it as text.

Also keep long numbers as text. Excel stores only 15 significant digits, so a phone number with an extension or a long international number can lose its last digits if it is entered as a number. The guide to keeping leading zeros in Excel covers this in detail.

Practise it#

Try the exercise on this page: one formula strips the dots and rebuilds the phone number with TEXT. Then practise TEXT and VALUE in a live spreadsheet, where you type each formula and it checks it instantly, and try the cleaning text lesson for messy imported data.