How to stop Excel from doing numbers E+?

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).

Scientific Notation

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.

Format Cells

Change the cell format to Number with a zero number of decimal places.

Format Cells Number Zero Decimal Places

Now, this problem doesn’t exist anymore.

Display Scientific Notation

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.

See also  How to handle prime numbers in Excel

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.

Text to Columns fixed width

Select the text from available Excel data types. The scientific notation will disappear from the cells.

text to columns text formatting

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.

Trim formula to remove scientific notation

Use the formula =TRIM(A1) to get rid of scientific notation.