Phone numbers
How to Fix Phone Numbers in Excel: Format and Country Codes
Store phone numbers as text, never as numbers, in the international E.164 form: a plus sign, the country code and the national number, digits only, at most 15 digits in total (for example +919876543210). Remove spaces, brackets and hyphens, drop the national trunk prefix where the country uses one (the leading 0 in the UK, for example), and add a country code only when you know the country. Excel can reshape numbers; it cannot tell you whether a number is valid in its country.
A phone number is an identifier, not a quantity, and that one fact explains most of the trouble. Excel loves to treat anything made of digits as a number: it drops leading zeros, switches long values to scientific notation and cuts off digits beyond fifteen. This guide shows how to keep numbers intact, how to clean the formats people type, how to add country codes safely, and how to check the result.
Why phone numbers get damaged
| What you see | What happened |
|---|---|
7700900123 instead of 07700900123 | Excel read the cell as a number and dropped the leading zero. That information is gone from the cell. |
9.19877E+11 | The value was long enough that Excel switched to scientific notation in General format. The digits may still be stored; the display is the problem. |
1234567890123450 instead of 1234567890123456 | A number longer than 15 digits was typed or pasted into a numeric cell. Excel keeps 15 significant digits (Microsoft documents this limit) and replaces the rest with zeros. That damage cannot be undone. |
(212) 555-0147, 212.555.0147, 212 555 0147 | People wrote the same number three ways. Duplicates and matches will fail until you normalize them. |
| A number with no country code | It was entered for local use. Anyone outside that country cannot dial it, and an SMS service will reject it. |
Ctrl+1, Text) before pasting or typing numbers, and when opening a CSV use Data, From Text/CSV and set the phone column to Text in the preview. Once a leading zero or a 16th digit is lost, only the original source can restore it.The target format: E.164
E.164 is the international numbering plan from the ITU. A conforming number contains only digits, at most fifteen, made of a country code (one to three digits) followed by the national number. Written for software, it is prefixed with a plus sign:
| Written locally | E.164 | Notes |
|---|---|---|
07700 900123 (UK) | +447700900123 | Drop the leading trunk 0 and add 44. 07700 900000 to 900999 is a range Ofcom reserves for drama use, handy for examples. |
98765 43210 (India) | +919876543210 | Indian mobile numbers have 10 digits; add 91. |
(212) 555-0147 (US/Canada) | +12125550147 | North American numbers have 10 digits after the country code 1. The 555-01xx block is reserved for fiction. |
0033 6 12 34 56 78 | +33612345678 | "00" is a common international call prefix; replace it with +. |
Why bother with one format? Messaging and calling services (SMS and WhatsApp APIs, for example) expect E.164, CRMs use it to detect duplicates, and a number in E.164 is unambiguous anywhere in the world.
Step 1: Keep what you have
- Copy the sheet. Keep the original phone column untouched.
- Format the working column as Text before you paste into it.
- If numbers already lost zeros, check the source. Restoring them yourself is guesswork unless every number is known to have the same length (for example
=TEXT(A2,"0000000000")for a fixed 10-digit format). - For ZIP and postal codes, the same rule applies:
=TEXT(A2,"00000")restores five-digit US ZIP codes whose zero was lost.
Step 2: Strip everything except digits and the plus
Move any extensions out of the way first (see below), because stripping non-digits would glue an extension such as "x123" onto the main number. Then, with Microsoft 365, which now includes regular-expression functions, one formula does it:
=REGEXREPLACE(A2,"[^0-9+]","")In older versions, nest SUBSTITUTE calls, one per character you want to remove:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"-",""),"(",""),")",""),".","")Also remove the non-breaking space with SUBSTITUTE(A2,CHAR(160),"") if the data came from a web page. Convert a leading "00" to "+":
=IF(LEFT(B2,2)="00","+"&MID(B2,3,20),B2)Step 3: Add the country code only when you know the country
This is where cleaning becomes risky. A ten-digit string can be an Indian mobile number, a US or Canadian number, or something from elsewhere, and nothing in the digits says which. A formula that adds +91 or +1 to everything creates wrong numbers that look right. Use these formulas only on a column where every row is from one known country, or add a country column and apply the rule per row.
| Everyone in | Formula (digits already in B2) |
|---|---|
| India (10-digit mobile) | =IF(LEFT(B2,1)="+",B2,"+91"&RIGHT(B2,10)) |
| United States or Canada | =IF(LEFT(B2,1)="+",B2,"+1"&RIGHT(B2,10)) |
| United Kingdom (national format with 0) | =IF(LEFT(B2,1)="+",B2,IF(LEFT(B2,1)="0","+44"&MID(B2,2,20),B2)) |
Numbers that already start with a plus are left alone. After applying a formula, copy and Paste Special as Values, and set the column to Text.
Step 4: Validate, honestly
Excel can check the shape of a number. In Microsoft 365 a regular expression is the strictest test (a plus sign, then 7 to 15 digits):
=REGEXTEST(B2,"^\+[0-9]{7,15}$")In older versions use a length test, which is looser because converting text to a number also accepts forms such as 1E3:
=AND(LEFT(B2,1)="+",LEN(B2)>=8,LEN(B2)<=16,ISNUMBER(--MID(B2,2,20)))Either confirms a plus sign and a plausible length (E.164 allows at most 15 digits, so up to 16 characters with the plus). It does not tell you whether the number exists in its country's numbering plan. Each country has its own rules for length and valid starting digits, and they change over time. The standard solution is Google's open-source libphonenumber library, which holds the metadata for every country and is used by Android and many applications to parse, format and validate numbers. If you validate by formula alone, a plausible but impossible number passes.
Extensions, multiple numbers and letters
- Extensions (
+14155550100 x123) are not part of E.164. Move them into their own column, for example=IFERROR(MID(A2,SEARCH("x",A2)+1,10),""), before stripping non-digits from the main number. - Two numbers in one cell ("+4477... / +4477...") should become two columns or two rows. Text to Columns with the slash as delimiter does it.
- Letters such as 1-800-FLOWERS are keypad spellings. Convert them with the keypad mapping (A-C=2, D-F=3, ...) or flag them for a person.
- Labels like "Mob:", "Tel." and "Phone" should be removed in step 2.
DecodingPro normalizes phone numbers using metadata derived from libphonenumber, the same library family used across mobile platforms, converts them to international format when the country is clear, and flags the ones it cannot place instead of guessing a country. Every change is logged. Test it free with 100 credits.
Clean a file freeStorage versus display
Store one clean value (E.164) and format it for people only where it is shown. A spreadsheet that mixes storage and display ends up with +91 98765 43210 in one row and 09876543210 in the next. If you need a readable column, keep it separate from the column your systems use.
Phone number checklist
- Column set to Text before data goes in.
- Original column preserved.
- Symbols, spaces and labels stripped; "00" turned into "+".
- Trunk "0" removed when a country code is added.
- Country code added only where the country is known.
- Length and shape checked; impossible numbers flagged, not "repaired".
- Extensions and second numbers moved to their own columns.
- One stored format (E.164), duplicates checked after normalizing.
Frequently asked questions
How do I keep the leading zero in Excel?
Format the cells as Text before entering or pasting the number, or type an apostrophe before it. When importing a CSV, set the column to Text in the import preview. If the zero is already gone, restore it from the source file.
How do I stop phone numbers turning into 9.19E+11?
Format the column as Text before entering the data. If a number is already displayed that way, widening the column or changing the format to Number with no decimals usually reveals the digits, provided it has 15 digits or fewer.
What is the international phone number format?
E.164: a plus sign, the country code and the national number, digits only, with no more than 15 digits in total. For example +447700900123 for a UK mobile number written locally as 07700 900123.
How do I add a country code to many phone numbers in Excel?
If every number is from one country, use a formula that adds the code and removes the national leading zero. For mixed lists, add a country column first. Never apply one country code to a mixed list.
Can Excel validate phone numbers?
Only their shape: plus sign, digits and length. To check whether a number is valid for its country you need numbering-plan data, which libphonenumber provides.
Sources
- E.164: the international public telecommunication numbering plan (digits only, maximum fifteen)
- Google libphonenumber: parsing, formatting and validating international phone numbers
- Microsoft: Excel specifications and limits (15-digit number precision)
- Microsoft 365 Insider blog: Regular expression (REGEX) functions in Excel
- Ofcom: telephone numbers for drama use