Stop Excel Using Scientific Notation on Numbers
Excel is brilliant!
It is so brilliant, that it’s helpful.
Excel is SO helpful THAT IT SUCKS!!!
Probably for 99.9% of the general population, all of Microsoft Excel’s cool features that help you format your data into something legible are something really cool. But I think Excel should have a “data only” setting.
What do I mean by “data only”? Have you ever tried debugging data in excel using all the cool functions, etc. to find duplicates, or sort the data only to find that Excel has gone and removed a leading “0”, or changed the number into Scientific Notation?
Apparently Excel has a 15 digit precision limit. But most of the time, I’d prefer it if Excel just treated the number as text until I want it sorted. Am I being TOO fussy?
Anyway, I found a great way to do this in 4 easy steps (once you’ve imported the data as text – see the comment below made on June 7th 2010 for reasons!):
- Add a new column to the right of your “number” column
- Add the formula UPPER() to the cell, with the contents of the brackets the cell to the left. Example (In cell “B1”): UPPER(A1)
- Drag that formula all the way down the column so that it matches the number of cells you need.
- Copy the column, and paste it as “Values” (Right-click, Paste Special).
I hope this helps somebody else.
And, now that I have THAT off my chest, back to work debugging 5000 records of credit card data using the excellent Excel!