In this lesson, you will learn how to disable scientific notation (e.g., 1,23457E+17) in an Excel spreadsheet.
This problem exists when you want to paste a long number. Pasting it to the spreadsheet, Excel changes the formatting to scientific notation (e.g. 1,23457E+17).
This problem is caused by Excel, which has a 15-digit precision limit. It's annoying, because most of the time, I'd prefer it if Excel just treated the number as text (until I wanted it sorted). You can solve this problem.
How to format the cell to disable scientific notation?
Just right click on the cell and choose Format cell.
Change the format from General to Number with a zero number of decimal places.
Now this problem doesn't exist any more.
- This solution works for other types of formatting as well. For example, Custom with a single 0 or Text.
- The number format does not affect the actual cell value, which is displayed in the formula bar.
- When Excel displays a cell with #### signs, it means that the cell is too narrow. Just make it wider.
How to concatenate the cell to disable scientific notation?
The other way is to concatenate the cells to display the full content of each cell.
A simple =CONCATENATE(A1) formula would do the trick. Thanks to that trick, you will not see "E+" any more.
It worked because that is how the concatenate function works. As an output, it creates a single string, and Excel does not apply scientific notation to such a string. It is worth mentioning that it does not change the format of the cell.
Using the Text to Columns Tool
You can also remove scientific notation with the Text to Columns tool. This tool is used to improve the content of cells in case of problems with their content or formatting.
To start, select the column that contains unnecessary scientific notations and go to the Excel ribbon. Click Data > Text to Columns.
In the first step, check the Fixed width option. Already at this stage you can see in the preview of selected data at the bottom of the window that scientific notation will be deleted.
In the third step, select the text from available Excel data types. When you click finish, the scientific notation will disappear from the cells.
The inconvenience is that green triangles will appear in the cells. If they bother you, check this link on how to remove green triangles from a spreadsheet in Excel.
Conversion of the cell to disable scientific notation
There is one more tricky way to accomplish the expected output that I would like to share with you.
There is a way to convert the content of the cell to the specific number format without changing it. Excel would stop using the scientific notation as well. This is the TEXT function that you need to use for that purpose.
=TEXT(A1,"0") is the Excel formula you should use.
The result is the same. "E+" disappeared from the cell.
Trimming to remove scientific notation
Another way to remove scientific notation from your workbook is to use the trim function.
The Trim function is used to remove excess spaces from a cell. Using the formula Trim only leaves single spaces between words.
As a side effect of using the Trim formula, you will get rid of scientific notation.
Use the formula = TRIM (A1) to get rid of scientific notation.