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):

  1. Add a new column to the right of your “number” column
  2. Add the formula UPPER() to the cell, with the contents of the brackets the cell to the left. Example (In cell “B1″): UPPER(A1)
  3. Drag that formula all the way down the column so that it matches the number of cells you need.
  4. 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!
;-)

6 Responses to “Stop Excel Using Scientific Notation on Numbers”

  1. Marcia  on February 6th, 2009

    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?

  2. james  on February 6th, 2009

    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 :-)

  3. Cosmo  on October 15th, 2009

    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

  4. James  on October 15th, 2009

    I’m really glad I could help you as well.

  5. Claire  on January 25th, 2010

    Thats very useful thanks..

  6. James  on January 25th, 2010

    I’m glad you liked it… :-)


Leave a Reply