May 18 2018 09:54 PM
May 18 2018 09:54 PM
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?
May 18 2018 11:08 PM
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.
Aug 02 2018 12:21 PM
Aug 02 2018 08:39 PM
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.
Aug 03 2018 05:19 AM - edited Aug 03 2018 05:43 AM
I have approximately 70,000 numbers in standard format. I create a new column and set the format to text. When I paste the numbers without formatting, they are left-justified, indicating text, but are also in scientific notation.
Aug 03 2018 05:43 AM
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.
Aug 03 2018 05:47 AM
They are already in number format. I need them in text format displaying the full string.
Aug 03 2018 06:14 AM - edited Aug 03 2018 06:15 AM
I suggest to use Text to Column instead as the below steps:
Mar 01 2021 08:37 AM
Apr 27 2022 03:34 PM
Nov 06 2023 03:29 PM
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?
Nov 06 2023 04:39 PM