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?
- 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 idea in the Excel's suggestion box asking for something similar.
Regards
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
