Whenever you type a number into Excel that contains more than 11 digits, Excel will automatically format the number using E+11 at the end of it.
Fortunately there are two ways to prevent Excel from doing this:
Method 1: Use Number Format
Method 2: Use TEXT Function
The following examples show how to use each of these methods in practice.
Example 1: Use Number Format to Prevent Excel from Showing E+11
Suppose we type the number 12345678911 into Excel:
This number contains exactly 11 digits so Excel is able to display each digit.
However, suppose we instead type 123456789111 into Excel:
Excel now formats the number as 1.23457E+11 since this number contains 12 digits.
To prevent Excel from doing this, we can click cell A1, then click the Number Format dropdown menu from the Home tab along the top ribbon and then click Number from the dropdown menu:
This will force the value in cell A1 to be displayed as a number in which all of the digits are shown and E+11 is no longer displayed at the end of the number:
Example 2: Use TEXT Function to Prevent Excel from Showing E+11
Once again suppose we type 123456789111 into Excel:
Excel formats the number as 1.23457E+11 since this number contains 12 digits.
Another way to prevent this from occurring is to type the following formula into cell B1:
=TEXT(A1, "0")
This formula uses the TEXT function with the argument “0” to format the number in cell A1 in such a way that all digits are displayed.
Note: You can find the complete documentation for the TEXT function in Excel here.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Round to Significant Figures in Excel
How to Remove Digits After Decimal in Excel
How to Convert a Fraction to a Decimal in Excel
Hi Zach,
Thanks for this. I process large data sheets with phone numbers / postcodes (or zip codes) that often start with ‘0’. When I try to convert to a number, it removes the ‘0’ from the start which makes the phone number incorrect. The ‘number’ option also tends to add decimal points which is also unhelpful. Unfortunately, selecting all and updating the cells to text doesn’t always work, as it keeps the E+11 at the end. The only way I’ve been able to fix it is by double-clicking in the cell itself, but this is not possible with a huge data set.
Any ideas on how to process this efficiently?
Thanks!
I hear you on this one. Excel can be frustrating when it tries to be “helpful” and reformats data in ways that actually break what you need. Phone numbers and postcodes are a perfect example because they aren’t really numbers you want to calculate with, they’re identifiers, and Excel doesn’t always handle that distinction well.
The simplest way to keep the leading zeros and avoid scientific notation is to treat them as text right from the start. If the data is coming from a CSV or another source, you can use the Text Import Wizard (or Power Query in newer Excel) and set that column to “Text” as it comes in. That prevents Excel from trying to reformat things for you.
If the data is already in the sheet, you can use a quick trick:
* Insert a new column next to your data.
* Use a formula like `=TEXT(A1,”@”)` or if you know the length of the numbers, something like `=TEXT(A1,”0000000000″)` to force it into text with leading zeros.
* Copy and paste the results back as values, then delete the helper column.
Another option if you want to clean everything at once is to use “Paste Special” → “Values” after multiplying by 1 in a helper column. That removes Excel’s auto-formatting and lets you reapply text format.
The key idea is to get Excel to stop treating those fields as numeric data. Once it’s locked as text, the zeros will stay, and you won’t see the unwanted decimal points or E+11 formatting.