In this lesson, you will learn how to disable scientific notation (e.g., 1.23457E+17) in an Excel spreadsheet.
This issue arises when you attempt to paste a long number. When you paste it into the spreadsheet, Excel changes the formatting to scientific notation (e.g., 1.23457E+17).
Excel causes this problem due to its 15-digit precision limit. Essentially, when a number exceeds this limit, Excel automatically formats it as scientific notation to maintain accuracy. This can be annoying, especially when you prefer Excel to treat the number as text until sorting or other specific actions are needed.
How to format the cell to disable scientific notation?
Just right-click on the cell and choose Format cell.
Change the cell format to Number with a zero number of decimal places.
Now, this problem doesn’t exist anymore.
Tips:
- 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.
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.
Select the text from available Excel data types. The scientific notation will disappear from the cells.
The inconvenience is that green triangles will appear in the cells.
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.
Use the formula =TRIM(A1) to get rid of scientific notation.
