Forum Discussion
scientific notation forcing itself into my sheet
I down loaded a comma separated values spreadsheet to excel. One column is upc codes. many codes in it were converted to scientific notation upon download.
I saved the file as an excel spreadsheet. I expanded the column.
I changed the column format type to number.
Excel then took the upc codes out of scientific notation.
Now, I need to add back the zeros that were at the beginning of some of the upc's, which were stripped out during the export.
In order to do that, I either have to change the column to text format, or to a custom format.
If I switch it to text, a few of my upc's go back to scientific notation.
If I use custom, and i type in how I want the column to look, then every single upc gets at least 2 zeros added to it. This is ridiculous.
How do i get this sheet to display leading zeros AS NEEDED, when entered as such, without every single cell getting extra zeros, and without it converting the numbers back into scientific?????
2 Replies
It depends on what do you mean under "I down loaded a comma separated values spreadsheet to excel". If just double click on csv file to open it in Excel - no, where is no way to prevent such conversion, but you may vote for this idea here Stop Excel from changing large "numbers" (actually text values) to scientific notation. – Welcome to Excel’s Suggestion Box! (uservoice.com)
The only way is to assign text type to such columns during transformation. As AnaBoyko mentioned you may use Power Query or, alternatively, legacy From Text connector where assign text type on the third step of the wizard.
Depends on what do you plan to do further. If you just want to see these zeros and the nuber of digits in your data is the same, you may solve it with format.
But if you plan to do some calculations with these data, you need to re-download your data into the colomn pre-formated as text or use Power Query to download.
