Forum Discussion
Unwanted automatic change of text number format to scientific
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?
- 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.
- Thank you! This solved my problem.
