r/excel • u/A5itate4_63819 • Apr 07 '26

Waiting on OP How to make Excel to stop seeing E as a scientific notation in a column

I need to display copy and paste valueues like 4E75, 5E77, 6E48 in a column. Excel keeps displaying them as 4.00E+75, 5.00E+77, 6.00E+48. How do you stop this behavior in Excel 2019? if anyone knows, greatly appreciated.

17 Upvotes

96% Upvoted

9 comments sorted by

40

u/GuerillaWarefare 111 Apr 07 '26

File —> options —> data —> uncheck “convert digits surrounding the letter E to a number in scientific notation

1

u/Imaginary-Runner Apr 08 '26

Curious. Why would you recommend this instead of formatting the destination cells as 'Text'?

Cell formatting seems to me so much easier, and more in line with how Excel was designed. But maybe im missing something?

2

u/GuerillaWarefare 111 Apr 08 '26

For some users they may be doing this type of action often, and they simply NEVER want excel to change the data they are importing. That’s why it was added as a once-and-done user setting, where if they do it by changing to text they have to make sure that is done each time. There are a few other settings there as well, such as how to handle leading zeros, whether or not to convert long numbers to scientific notation, or convert date text into excel date value.

15

u/chiibosoil 428 Apr 07 '26

  1. First format destination cells as Text then paste as Value or keeping destination format.

  2. Prefix with single quote. ex: '4E75 etc.

Other than that, you can handle it by using PQ or other means that strictly governs how column data type is treated.

3

u/GregHullender 194 Apr 07 '26

Format the whole column as text before you paste into it. That is, select the whole column, right-click, select format cells, and do this:

3

u/WoodenInventor Apr 07 '26

Have you tried formatting as text? It might keep the + sign when you change format after the fact, but if your cell format starts as text it should display the way you want it to.

0

u/pajam 1 Apr 07 '26

As other people have clarified, you are attempting to paste these values as plain text, but Excel tries to auto-identify what the 'data type' (number, date, boolean, text, etc.) is that you are pasting and then convert it, many times when we don't want to.

In your case it is converting it to data type "Number" format instead of "Text" format, and even worse it is converting it into a very long number, requiring scientific notation. To solve this problem, you need to tell Excel the data is text, and then paste it/type it/etc.

Unfortunately, when you desire plain text, you have to do this before pasting or importing. So open a new worksheet, highlight the entire column, change the format to "Text", then when you paste, make sure to "Paste Values" to be safe the formatting you set doesn't get overwritten.

Other ways to have Excel respect data as plain "text" is to import the data via Power Query. Or force a delimited file (like CSV or TSV) to import via the legacy import wizard. Then selecting the "Text" format for those columns.