Forum Discussion
Unwanted automatic change of text number format to scientific
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 https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10374741-stop-excel-from-changing-large-numbers-actually in the Excel's suggestion box asking for something similar.
Regards
Haytham
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 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.