Forum Discussion
Unwanted automatic change of text number format to scientific
I have a simple spreadsheet in which I was trying to list "codes" for keeping track of data for a game I'm working on. (Table top dice game) I find that the codes 01P2 or 01G2 can be found and replaced with 02 or 03, etc. However, my code 01E2 etc. changes automatically to scientific format. Apparently something about the "E" triggers some sort of auto response.
I looked around in options but found nothing that seems to apply to this. I tried chat but the techie wanted to take over the computer, which sounded to me like overkill. He transferred me to second techie who dumped me out of the chat. First time ever I gave feedback of one star.
Anybody have a simple solution?
16 Replies
You will find a setting under Options/Data/Automatic Data Conversion which sets
"Convert digits surrounding the letter "E" to a number in scientific notation"
In response to the old comment
"They need to disable this dumb "feature". I can't imagine a single person on the planet that finds it useful",
It is not a dumb feature because anyone involved with scientific work or engineering is likely to require such notation for almost every number in the workbook. 1.24E+12 is a standard way of representing the number 1237650000000 to 3 significant figures. To ask otherwise is the same as expecting a leading "-" to be treated as a hyphen followed by text. I does happen, but Excel is really about numbers and calculation.
Sir that did not work, i do believe I posted that it didn't work in my comments. I figured it out. It's a dumb feature to have automatically enabled. Everyone that uses Excel is not an Engineer or works with scientific work. Those sort of features should be optional, the user should have to select those options. Not forced on you, then your directions don't work to remove that feature.
Tried a few solutions and none of them work. Why is this an AUTOMATIC SETTING, this type of option should be selected by the individual user. Now my work is delayed.. I just want to type and save, why does MICROSOFT make it so dang complex?!!! WHATEVER OPTIONS A USER WANTS, THEY SHOULD HAVE TO SELECT. NOT BE FORCED ON YOU, now all the options that are supposedly fixes are not working SO now what?
- They need to disable this dumb "feature". I can't imagine a single person on the planet that finds it useful.
- Absolutely, I have about 200 cells of hyperlinked tracking numbers that I cannot simply copy and paste due to this "feature". Instead I have to input them manually and then hyperlink them. A task that should take 30 minutes is now going to take me a couple of hours, just brilliant.
Hi John,
You have an option to preset the Text format on empty cells before you enter the code.
Select the targeted cells, and press Ctrl+1 to open the Format Cells dialog box.
After that, just select the Text category, and then hit OK.
By the way, there is an https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10374741-stop-excel-from-changing-large-numbers-actually in the Excel's suggestion box asking for something similar.
Regards
Haytham
Thank you this worked, dang shame that Microsoft tell you to do something that don't work. But I find the real working solution in the community, Thanks again Haytham
- This doesn't prevent the conversion to scientific notation. You just get a text version of the scientific notation.
Hi,
This is because you have applied text formatting to non-empty cells!
What I said before: "You have an option to preset the Text format on empty cells before you enter the code."
The idea is to set the text format first, and then enter the numbers to prevent the automatic conversion to the scientific notation.
You can also preset the cells format in Number, Currency, or Accounting, this also works.
If you have numbers with the scientific notation, then you have to change the format to one of these: Number, Currency, or Accounting.
Regards
