Unwanted automatic change of text number format to scientific

[John Nedby]

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? 

[#2]

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

[#3]

This doesn't prevent the conversion to scientific notation. You just get a text version of the scientific notation.

[#4]

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

[#5]

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

[#6]

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.

[#7]

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

[#8]

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

[#9]

Thank you! This solved my problem.

[#10]

They need to disable this dumb "feature". I can't imagine a single person on the planet that finds it useful.

[#11]

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.

[#12]

@Haytham Amairah 

It doesn't work when parsing.

1234-1E001 becomes two colums with the scientific equivelent of 1E001 in the second column no matter if you choose text or not.  I tried to trick it and chage all the 1E to '1E before and then after parsing change it back by using find and replace. Then when I used find and replace it changed the cell format to scientific and displayes the number as 1.00+01 and it NEEDS to be 1E001, that is the number.  How do I fix this?

[#13]

I missed that you need to select both colums separately to set the format. I just changed the first column to text the 2nd was general. it works now when I select the destination column for the number and change that one to text.