To format phone numbers in Excel all the same way, strip each one down to its digits, drop a leading 1, and rebuild it with TEXT. Once D2 holds just the ten digits, 3125550187, =TEXT(D2,"(000) 000-0000") returns (312) 555-0187. In Microsoft 365 or Excel 2021, one formula does all three steps. Anything that doesn’t come out at ten digits, such as an extension, a foreign number or a short one, is marked Check for you to look at.
Phone numbers arrive the way people typed them: dots, dashes, spaces, a +1, an extension, sometimes two numbers in one cell. A dialer, a CRM import or a mail merge wants one format. Here’s the formula way in plain Excel, where it stops, and a faster way for a whole contact list.
The sample list
| A | B | |
|---|---|---|
| 1 | Name | Phone |
| 2 | Maya Brooks | 312.555.0187 |
| 3 | Owen Patel | (312) 555-0142 |
| 4 | Lena Ortiz | +1 312-555-0156 |
| 5 | Grant Hale | 3125550110 |
| 6 | Iris Novak | 1-312-555-0164 |
| 7 | Theo Walsh | 312 555 0123 x45 |
| 8 | Nora Kim | +44 20 7946 0958 |
| 9 | Sam Reyes | 555-0131 |
| 10 | Pat Doyle | (000) 000-0000 |
| 11 | Jo Lin | 312-555-0120 / 312-555-0121 |
To type the +1 and +44 rows yourself, start them with an apostrophe ('+1 312-555-0156) so Excel keeps them as text.
Step 1: Strip each number to its digits
In C2, filled down:
Digits only (Microsoft 365 or Excel 2021)
=CONCAT(IFERROR(MID(B2,SEQUENCE(LEN(B2)),1)*1,""))
SEQUENCE lists the positions 1, 2, 3 and so on. MID takes the character at each one, *1 turns a digit into a number and anything else into an error, IFERROR drops the errors, and CONCAT joins what’s left. “+1 312-555-0156” becomes 13125550156.
SEQUENCE needs Microsoft 365 or Excel 2021. In current Microsoft 365 there’s a shorter way to do the same thing: =REGEXREPLACE(B2,"[^0-9]",""), which replaces everything that isn’t a digit with nothing. More ways to pull digits out of text are in how to extract numbers from mixed text.
Step 2: Drop the leading 1
In D2, filled down:
US number without the country code (any version)
=IF(AND(LEN(C2)=11,LEFT(C2)="1"),RIGHT(C2,10),C2)
An eleven-digit number that starts with 1 is a US number with its country code. Keep the last ten digits. 13125550156 becomes 3125550156.
Step 3: Rebuild every number in one format
In E2, filled down:
The format you want (any version)
=IF(LEN(D2)=10,TEXT(D2,"(000) 000-0000"),"Check")
Ten digits get the format. Anything else is marked Check.
For another style, change the format in TEXT:
"000-000-0000"gives 312-555-0187."000\.000\.0000"gives 312.555.0187. The backslashes matter: without them, Excel reads the first dot as a decimal point.
For the +1 style, use =IF(LEN(D2)=10,"+1"&D2,"Check"), which gives +13125550187.
Tip: all three steps in one cell (Microsoft 365 or Excel 2021)
=IF(B2="","",LET(d,CONCAT(IFERROR(MID(B2,SEQUENCE(LEN(B2)),1)*1,"")),n,IF(AND(LEN(d)=11,LEFT(d)="1"),RIGHT(d,10),d),IF(LEN(n)=10,TEXT(n,"(000) 000-0000"),"Check")))
LET names the digits d and the Step 2 result n, so nothing is worked out twice. A blank cell stays blank.
When the column looks right, copy it and paste it as values into a new Phone column (Home, Paste, Paste Values). Keep the original beside it until the Check rows are sorted out: pasted over the original, the results would replace each of those numbers with the word Check. Then the helper columns can go.
What the formulas return
| B | E | |
|---|---|---|
| 1 | Phone | Formatted |
| 2 | 312.555.0187 | (312) 555-0187 |
| 3 | (312) 555-0142 | (312) 555-0142 |
| 4 | +1 312-555-0156 | (312) 555-0156 |
| 5 | 3125550110 | (312) 555-0110 |
| 6 | 1-312-555-0164 | (312) 555-0164 |
| 7 | 312 555 0123 x45 | Check |
| 8 | +44 20 7946 0958 | Check |
| 9 | 555-0131 | Check |
| 10 | (000) 000-0000 | (000) 000-0000 |
| 11 | 312-555-0120 / 312-555-0121 | Check |
Where the formulas stop
Five rows came out right. The other five are where the time goes:
- Extensions. “312 555 0123 x45” becomes twelve digits, 312555012345, and lands on Check. Keeping the extension takes another formula for each way people type it: x, ext., ext or #.
- Country codes. The formulas assume US numbers. The UK number was fine as typed, and it lands on Check.
- Two numbers in one cell. “312-555-0120 / 312-555-0121” becomes twenty digits and lands on Check.
- Fakes. (000) 000-0000 has ten digits, so it gets formatted as if it were real. So would 1111111111.
- Short numbers. 555-0131 is marked Check, which is right, but nothing says why.
Tip: one format before you look for duplicates
312.555.0187 and (312) 555-0187 are the same number, but Excel won’t match them until they’re in one format. Format the phones first, then find and remove duplicates.
Common questions
Why doesn’t a custom number format change my phone numbers?
Because the cells hold text. A number format such as (000) 000-0000 only changes how a number looks, and a phone number typed with dots, dashes, spaces or brackets is stored as text. Even 3125550187 stays as it is if the cell holds it as text. And where the format does work, the cell still holds 3125550187; only the display changes. TEXT, in Step 3, gives you (312) 555-0187 as the cell’s actual value.
I don’t have Microsoft 365. What works in older Excel?
SEQUENCE and LET need Microsoft 365 or Excel 2021, and CONCAT needs Excel 2019 or later. In older versions, take out the usual characters one by one with SUBSTITUTE: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," ",""),"(",""),")",""),"-",""),".",""),"+",""). Put it in C2 in place of the Step 1 formula, and Steps 2 and 3 work as they are. Anything the formula doesn’t list, such as the x before an extension or a slash between two numbers, stays in, so those rows land on Check.
Which phone format should I use for a CRM import?
The +1 style, +13125550187. The country code travels with the number, so the importer doesn’t have to guess the country. In our own import test, monday CRM read some numbers in the (646) 555-0113 style as New Zealand numbers and left others with no country; with the +1 in front, every one came in as a US number. With the formulas, use =IF(LEN(D2)=10,"+1"&D2,"Check") in place of Step 3. In Excel Data Cleaner, pick the +1 style in the Phones step.
A whole contact list: Excel Data Cleaner
On a short list you fix the Check rows by hand. On a long one, every Check row is a lookup by eye, and the fakes hide among the rows that look fine.
Excel Data Cleaner formats the whole phone column in the Excel you already have. In the video, a list typed every which way is loaded, the phone column is tagged, and the Phones step gets a country and a format. Then every result is reviewed before it’s applied: extensions in their own column, a second number set aside, the too-short and fake numbers flagged, and the UK number, which already has its country code, left as it is. Approve, Finalize, and it’s one format all the way down.
A whole contact list of phone numbers?
The formulas handle the tidy rows and leave you the rest: extensions, foreign numbers, two numbers in a cell, and fakes that look real.
Excel Data Cleaner puts the whole phone column into the format you pick, (555) 123-4567, 555-123-4567, 555.123.4567 or +15551234567, with country-aware detection for the US, UK, Germany, France, Mexico, Brazil, India, Japan, China and Australia. A default country covers numbers with no prefix, and it can move the country code into its own column. It separates extensions and flags what it can’t format, and you review and approve every change before it is applied. Free to try with up to 100 records.
Headed for a CRM? Add-on exporters turn the cleaned list into an import-ready file for HubSpot, Salesforce, Zoho, Pipedrive or monday. For a CRM import, pick the +1 style.
See Excel Data CleanerWindows PC required: it runs inside Excel on Windows, not on a phone, a tablet or a Mac. Why it does not run on Mac.
Watch it done, 2:13. A column of phone numbers typed every which way, put into one format in Excel Data Cleaner.
Phone lists arriving messy every week?
Tell us about your data and we’ll build a tool that handles it.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.