Excel and Google Sheets formula to format phone numbers
Pick the country of your numbers, the format you want (E.164, international or national) and what is wrong with them: spaces and dashes, a leading 0, a missing +, 00 instead of +. You get one formula for Excel 365 and Google Sheets, a SUBSTITUTE version for older Excel, and a preview of what it returns for sample cells. A formula fixes the format; it can't tell whether a number is real.
Free · no sign-up · runs in your browser, nothing is uploaded
+44 · trunk prefix 0 · national numbers 9 to 10 digits
Excel 365 and Google Sheets (put in the cell next to A2, fill down)
=LET(txt,IF(ISNUMBER(A2),TEXT(A2,"0"),TRIM(A2)),hasplus,LEFT(txt,1)="+",dig,REGEXREPLACE(txt,"[^0-9]",""),idd,AND(NOT(hasplus),LEFT(dig,2)="00"),isintl,OR(hasplus,idd),rest,IF(idd,MID(dig,3,99),dig),home,LEFT(rest,2)="44",nsn,IF(isintl,MID(rest,3,99),IF(AND(LEFT(dig,2)="44",LEN(dig)>=11,LEN(dig)<=12),MID(dig,3,99),IF(LEFT(dig,1)="0",MID(dig,2,99),dig))),IF(txt="","",IF(dig="","CHECK: "&txt,IF(AND(isintl,NOT(home)),"+"&rest,IF(OR(LEN(nsn)<9,LEN(nsn)>10),"CHECK: "&txt,"+44"&nsn)))))
Older Excel (2021 and earlier, no REGEXREPLACE): two columns with SUBSTITUTE
1. In B2: the digits only (SUBSTITUTE removes each separator)
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(A2),TEXT(A2,"0"),TRIM(A2))," ",""),"-",""),".",""),"(",""),")",""),"/",""),"+",""),UNICHAR(160),"")2. In the next column: the formatted number (long, because older Excel has no LET)
=IF(IF(ISNUMBER(A2),TEXT(A2,"0"),TRIM(A2))="","",IF(B2="","CHECK: "&IF(ISNUMBER(A2),TEXT(A2,"0"),TRIM(A2)),IF(AND(OR((LEFT(IF(ISNUMBER(A2),TEXT(A2,"0"),TRIM(A2)),1)="+"),AND(NOT((LEFT(IF(ISNUMBER(A2),TEXT(A2,"0"),TRIM(A2)),1)="+")),LEFT(B2,2)="00")),NOT((LEFT(IF(AND(NOT((LEFT(IF(ISNUMBER(A2),TEXT(A2,"0"),TRIM(A2)),1)="+")),LEFT(B2,2)="00"),MID(B2,3,99),B2),2)="44"))),"+"&IF(AND(NOT((LEFT(IF(ISNUMBER(A2),TEXT(A2,"0"),TRIM(A2)),1)="+")),LEFT(B2,2)="00"),MID(B2,3,99),B2),IF(OR(LEN(IF(OR((LEFT(IF(ISNUMBER(A2),TEXT(A2,"0"),TRIM(A2)),1)="+"),AND(NOT((LEFT(IF(ISNUMBER(A2),TEXT(A2,"0"),TRIM(A2)),1)="+")),LEFT(B2,2)="00")),MID(IF(AND(NOT((LEFT(IF(ISNUMBER(A2),TEXT(A2,"0"),TRIM(A2)),1)="+")),LEFT(B2,2)="00"),MID(B2,3,99),B2),3,99),IF(AND(LEFT(B2,2)="44",LEN(B2)>=11,LEN(B2)<=12),MID(B2,3,99),IF(LEFT(B2,1)="0",MID(B2,2,99),B2))))<9,LEN(IF(OR((LEFT(IF(ISNUMBER(A2),TEXT(A2,"0"),TRIM(A2)),1)="+"),AND(NOT((LEFT(IF(ISNUMBER(A2),TEXT(A2,"0"),TRIM(A2)),1)="+")),LEFT(B2,2)="00")),MID(IF(AND(NOT((LEFT(IF(ISNUMBER(A2),TEXT(A2,"0"),TRIM(A2)),1)="+")),LEFT(B2,2)="00"),MID(B2,3,99),B2),3,99),IF(AND(LEFT(B2,2)="44",LEN(B2)>=11,LEN(B2)<=12),MID(B2,3,99),IF(LEFT(B2,1)="0",MID(B2,2,99),B2))))>10),"CHECK: "&IF(ISNUMBER(A2),TEXT(A2,"0"),TRIM(A2)),"+44"&IF(OR((LEFT(IF(ISNUMBER(A2),TEXT(A2,"0"),TRIM(A2)),1)="+"),AND(NOT((LEFT(IF(ISNUMBER(A2),TEXT(A2,"0"),TRIM(A2)),1)="+")),LEFT(B2,2)="00")),MID(IF(AND(NOT((LEFT(IF(ISNUMBER(A2),TEXT(A2,"0"),TRIM(A2)),1)="+")),LEFT(B2,2)="00"),MID(B2,3,99),B2),3,99),IF(AND(LEFT(B2,2)="44",LEN(B2)>=11,LEN(B2)<=12),MID(B2,3,99),IF(LEFT(B2,1)="0",MID(B2,2,99),B2)))))))
Preview (the same steps as the formula, run in your browser)
| Cell | Formula returns |
|---|---|
| +44 740 012 3456 | +447400123456 |
| 0740-012-3456 | +447400123456 |
| 00447400123456 | +447400123456 |
| 447400123456 | +447400123456 |
| (740) 0123456 | +447400123456 |
| +49 20 7946 0958 | +492079460958 |
| 74001234 | CHECK: 74001234 |
A formula tidies the format; it can't tell whether a number is real. It doesn't know which ranges a country assigns, or rules such as Argentina's 9 for mobiles. Check the result with the phone number validator, or clean a whole file with the phone list cleaner.
Numbers stored as numbers have lost their leading 0 and, beyond 15 digits, their last digits. The formula reads what is left; it can't bring lost digits back.
Runs entirely in your browser. Nothing you type, paste or upload is sent to MobileValidate or anyone else, and nothing is stored. The page counts one anonymous "tool used" event per visit (no numbers, no text), and sends nothing at all when your browser asks not to be tracked.
A tidy number is not always a live one
The formula gets the format right. Whether the number is switched on, which network it is on or whether it is on WhatsApp takes a real check. Run the cleaned column as a bulk job; the estimate is free.
How it works
The formula works through the same steps for every cell: read the text (or turn a number cell into its digits with TEXT(cell,"0")), note a leading +, keep only the digits, then decide what the digits are. A leading + or 00 means the country code follows. Digits that start with the country code and have the right length for it are an international number without the +. Digits that start with the trunk prefix (0 in most countries) are a national number. Anything else is taken as the national number without its 0, which is what a spreadsheet leaves when it drops the leading zero.
The output is +, the country code and the national number for E.164 (+447400123456), the same with a space after the country code for international (+44 7400123456), or the trunk prefix and the national number for national (07400123456). Grouping the remaining digits differs by country and number type, so the formula doesn't add more spaces. A number with another country's code keeps it, as + and digits. With the length check on, a result that is too short or too long for the country comes back as CHECK: and the original, so you can filter those rows.
Country data for 238 countries (calling code, trunk prefix and the shortest and longest national numbers) comes from libphonenumber, the open-source numbering-plan data most phone apps use. Countries we don't serve are not offered.
Excel 365 and Google Sheets get one LET formula with REGEXREPLACE. Older Excel (2021 and earlier has no REGEXREPLACE) gets two columns: a helper column that removes spaces, dashes, dots, brackets, slashes, + and non-breaking spaces with SUBSTITUTE, then the same steps written out without LET. The preview runs the same steps in your browser on the sample cells, so what you see is what the formula returns. Nothing you type is sent anywhere.
Last updated
What a spreadsheet formula can't do
- Tell whether a number is valid. The formula checks the length, not whether the number is in a range the country assigns. The phone number validator does that check for up to 500 numbers.
- Apply country rules. Argentina adds 9 after +54 for mobiles, Mexico dropped the 1 after +52, some countries keep a 0 inside the number. Libraries know these rules; a formula doesn't. The phone list cleaner applies them to a whole CSV file in your browser.
- Bring back lost digits. A column stored as numbers loses its leading zeros, and digits after the fifteenth turn into zeros. Shown as 4.47E+11, the value is often still complete; past 15 digits it is not. Import the column as text next time.
- Tell whether the number is live. Only the mobile network knows whether a number is switched on today: an HLR lookup asks it.
Frequently asked questions
How do I format phone numbers as E.164 in Excel?
Pick your country and E.164 above, copy the formula into the cell next to your first number (B2 if the numbers start in A2) and fill it down. It removes spaces and dashes, drops a leading 0, adds + and the country code, and leaves numbers that already have a country code with it. Then copy the column and paste it as values.
Does the formula work in Google Sheets?
Yes. Google Sheets has both LET and REGEXREPLACE, so the first formula works there unchanged. Paste it into the first cell and drag it down, or double-click the fill handle.
My Excel says #NAME?. What now?
Your Excel doesn't have REGEXREPLACE, a recent addition to Excel for Microsoft 365, or LET (Excel 2021 and later). Use the older-Excel version: the first formula goes in a helper column and keeps only the digits, the second one formats them. Both use only functions that Excel 2013 and later have.
Why do my numbers show as 4.47E+11?
Excel stored them as numbers, shows big numbers in scientific notation and drops the leading 0. Tick "cells stored as numbers" and the formula reads the digits with TEXT(cell,"0"). Numbers longer than 15 digits have already lost their last digits; import the column as text instead (Data, From Text/CSV, column type Text).
Can a formula check whether a phone number is valid?
Only its length. Whether a number is in a range the country assigns needs numbering-plan data, which the phone number validator has. Whether it is switched on needs the network: an HLR lookup.
What does CHECK: in the result mean?
The digits are too short or too long for a number of the country you picked, or the cell has no digits at all. Typical causes are a missing digit, two numbers in one cell, an extension or a number from another country written without its country code. Filter the column on CHECK: and fix those rows by hand.
Is anything I type sent to MobileValidate?
No. The formula and the preview are built in your browser. Nothing you type is sent or stored.