Unwanted automatic change of text number format to scientific

Occasional Visitor

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? 

9 Replies

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

I have approximately 70,000 numbers in standard format. I create a new column and set the format to text.  Result of pasting values onlyResult of pasting values onlyFormatting of blank cellsFormatting of blank cellsOriginal data in number formatOriginal data in number formatWhen I paste the numbers without formatting, they are left-justified, indicating text, but are also in scientific notation.

The technique I mentioned before doesn't work if you copy the numbers and paste them into a location has the Text format!

It works only when you enter the numbers manually!

 

In your case, just select the entire range of numbers and change the format in place to one of these: Number, Currency, or Accounting.

They are already in number format.  I need them in text format displaying the full string.

I suggest to use Text to Column instead as the below steps:

  • Select the numbers
  • Go to Data >> Data Tools >> Text to Columns
  • Click Next button twice to reach out to the Step 3 of 3
  • In Step 3, select Text, and select a destination cell
  • Click Finish

Text to Column.png

 

Thank you! This solved my problem.
They need to disable this dumb "feature". I can't imagine a single person on the planet that finds it useful.