Forum Discussion
Keeping exact numbers (stop rounding/scientific notation)
I have one column that lists various receipt IDs that include a variety long or short numbers or text (Example: 3666979/JU; 20001010905018546; R00017/2020; 16773...). I just want to keep the exact number/text within cell without any formatting changing it. I've tried a couple solutions with no real luck from several queries online, I keep either getting scientific notation for long numbers or having the number round up or round down. Changing the cell format to "Number" results in scientific notation and solutions for that say to change the format to "Text" or to add an apostrophe in front of it. Changing the format of the cell to "Text" results in the number rounding up or down (ex. 20001010905018546 changes to 20001010905018540). There are no decimals places involved, so I do not need to adjust the decimal place like many solutions only say to do to stop rounding. How can I resolve this to just keep the data unchanged when entered? My other columns use formatting the way I need, so this is the only column that I cannot resolve. Any help would be appreciated! Note: I'm on Excel 2016 for Mac.
- MAC --
The only way I can see to do this would be to use the Outline Number field. Insert that field in the Grid view and take a look at the numbering scheme. Perhaps you can use the data from this field to help you generate your Power BI report. Hope this helps.
4 Replies
- I want Excel to stop doing stupid stuff. Stop converting long numbers into a completely useless and idiotic "scientific notation".
And it is not just for my sake. I have clients who use my software to get automatic reports of devices with all numeric Serial Numbers, ENS, IMEI... Converting these numbers to "scientific notation" is pure idiocy. larc91 I have the same issue. I have tried every solution found on a search, including deleting the content and editing the cell format to text. It then converts to scientific notation. I need the full number. The problem here is that I have thousands of numbers in excel and I don't really want to manually type all of them. Is there a better solution to stop excel from rounding my numbers?
- The easiest way to deal with this is to select the columns with a CTRL + Click, then right click and select "Format Cells". In the Format Cells menu, select "Custom" and enter a bunch of #################################, then click "Ok". This will convert all the numbers back to normal.
Try it in this order:
1. Delete current cell content.
2. Change number format to "Text".
3. Type the ID into the cell.
