Forum Discussion
Unwanted automatic change of text number format to scientific
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.
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
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?
