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!
Thank you that was really helpful! Question: I created a macro(find and replace tens of data) to replace a certain value for something like this 427,161,116,214,215,429
Instead Excel transforms that into this: 4.27161E+17
When I apply your “UPPER” tip it becomes this: 427161116214215000
The comma separators are gone! As well as changing the last three digits of my array to zeros!
Do you have a tip to help me with that?
Hi Marcia.
Any chance you could forward me the macro and some sample data?
I’d just like to see:
- Where the value is you’re setting it to (entered? from another cell?)
- What values you’re trying to set
- How the macro is setting it.
I’d love to help
hey, Thanks a million. I work with UPC and ICPNs often and it is always an issue. Sometimes I hate excel – other times it is a life saver. There are times when I’ve double-clicked hundreds of rows to remove the exponential notation. You have saved me HOURS of time. CCC
I’m really glad I could help you as well.
Thats very useful thanks..
I’m glad you liked it…
I’m soooo glad you posted this great tip!!! I crown you geek of all geeks (I hope that’s considered a super honor)
If it is a .csv file, rename it to .txt, then import it into excel. There is a section in the import procedures where you can select the fields and import them as TEXT instead of GENERAL format.
Thanks bryan. I did say in the article that this had to be done “once you’ve imported the data as text” but I’ll make that bold.
If you don’t import the data as text first, (for example, using the method you mentioned), then it won’t help converting the scientific notation to text, because Excel will change the last few numbers to “0″.
No idea why…