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…
You got that right — it is so helpful that is SUCKS. Why can’t the Microsoft folks figure out that users really do want to be able to control (as in turn off) ALL of the “helpful” features??? If only there was a single setting “leave my data the F alone” box.
I am attempting to bring in data from a website that includes addresses — so first “converting” the columns to text then importing into Excel will not work. What I need to do is copy the website report (displayed in columns) then paste into Excel (which is friendly enough to convert one of the key numbers into scientific notation, destroying the last digit).
Anybody know a way to make Excel leave the format alone?
My great pleasure
ah, brilliant, bless you!
Had same issue with received spreadsheet containing book ISBNs (13-digits). Tip works great. I hate software that thinks it’s smarter than I am 🙂
I was having a similar problem. When I imported a column containing ID numbers, I had to remove all spaces (using Ctrl F and replace) from the column to use the data to run a comparison to another database where the spaces did not exist. Upon doing this all the numbers containing an “E” were converted to scientific notation despite the fact that I set the cell formats to “text” in the column before performing the operation.
I fixed this by replacing E with Ë in both databases then removing the spaces in the latter import. Again by using Ctrl F.
Quick and simple!
RJH
Just use Openoffice, which is free and does not do this silliness. This solution works, but is way to cumbersome.
15 digit accuracy limit is ridiculous.
In my problem, I had to export the data from another system as a .csv file. My numbers were 20 digits long and they were mixed in with other columns of data. What I found was when I opened the file in excel it changed all of those values to E and cutting off the rest of the numbers. I found a 4 step solution to this problem, this is good especially if you’re working with say 3000 line items of numbers like this, or other complex data along with it.
1)Open a new workbook
2)Select every cell/row and “format cells” as a “text”
3)Open your .csv file in NOTEPAD, “Select all,” “copy”
4)Go back to your workbook, “Paste Special,” “text”
Presto!!! You should have all of columns aligned properly and all of your numbers should just be numbers, no E’s and no data lost!!
Hope this helps, because this frustrated the hell out of me for months.
Cheers!!
I also have this problem with ISBNs and SKUs. I ended up opening the csv, and formatting the relevant columns as ‘Number’ with 0 decimal places. I also unchecked the ‘Use 1000 Separator (,) which seems to work just fine.
Many many thanks, your simple and genious solution solved my troubles!
oh sweet Jesus, thank you!!
I deal with a million UPC numbers in my day job and between Excel turning everything into scientific notation and “helpfully” taking off leading zeros, I was ready to tear my hair out…
This was genius. Thank you so much for documenting it.
Awesome. Cheers
Great solution. I feel like I run into issues with how excel formats/interprets the data pretty much every time I break out vlookup. It’s not often that I can find a quick and easy solution to the problem online, but you nailed it on both fronts. Thanks for the post.
Excel is so messed up it takes a community to figure it out. I bookmarked this post on my blog so I can find it the next time I need to do this. Thanks so much !
something that worked for me for upc codes was to go into format data cells… under the data types… I selected Custom and just put a bunch of #’s and it stopped as well.
I found if you no longer have the .csv file you can copy the column or row of numbers from Excel into Notepad or Ultraedit. Then copy from Notepad and “paste value” as text back into Excel.
Greetings folks, just thought I might offer another solution in Excel helpful for processing data feeds.
Format Cells –> click Custom, then type “000000000000” (or 13 0’s for EAN), click ok.
Entries that start with 0 are shown properly, and scientific notation is eliminated. You can now save to text tab delimited or csv with no worries.
Cheers,
Bruce
Why thanks a heap, saved me a lot of faffing
The only way I know to store a large number in Excel (15 characters) or less in a column column but force Excel NOT to store or display it in scientific notation is the following:
1. Change the column format for your numbers to TEXT (right-click column header and choose format and then text as the format). You may still see the numbers in scientific notation, so do number 2 next.
2. Starting on the first row for that column, select it and click F2 twice. That resets the number from being a number plus translates it into text format. (using the down arrow keys do this for all your values in the text clumn).
* You can also set up a text formatted column and past the numbers into it.
This also solves numerous data import and export issues when moving data between say a CSV, Access DB, or SQL Server and Excel. If you dont translate the numbers to TEXT format and then reset the scientific notation displayed, these other programs read whats displayed (not stored in Excel) which will pull the scientific notation values for these numbers, which is NOT what you want.
It is only a great solution until you try and batch edit the numbers, like do a find and replace on a “-” or remove a letter like a “P” so that you isolate the number. Then, you will happily find scientific notation again and conversion of the sixteenth digit to a zero.
I understand precision, but I turned it off because it does not calculate values based on what you see. It does not store it that way.
So, what is the solution? I can’t keep text as text or even trust the numbers that I am using!
Very very helpful, thank you so much!
I am having a similar problem with alphanumeric room numbers. They are in the form:
Building Name/Floor Level/Room Number (i.e. Tower/03/03B14)
The room numbers are all alphanumeric. I need the room numbers broken out separately. Most are fine, but when they contain an ‘E’, Excel sees the the alphanumeric text as scientific notation no matter how the cells are formatted. So I can’t use the the Text-to-Columns tools which would be the easiest way to do this. The different building names, floor levels and room numbers have differing numbers of digits as well, so a straight forward LEFT, MID or RIGHT function doesn’t work either.
Any help would be appreciated.
Nice one. I have been wanted to know how to do this forever.
Put literal quotes around it before you send it to CSV.
Hi Rich, sorry I didn’t see your comment.
You could try what Jamonit says, or, if possible, put a ‘ character before each room number.
I’m not sure what you need to do with the values or if this ‘ character will mess around with anything, but it will prevent the modification of any numbers.
RICH,
See my comment. – November 23, 2010
Thanks for the comments. I eventually got it to work using functions that only applied to text, but don’t remember what I did and can’t find the spreadsheet anymore. Ultimately I found out that the info in the spreadsheet was incorrecy and therefore I wasted my time. lol.
Hi Rich
Use Paste special and select Text, then try these:
A1 Tower/03/03B14
B1 =LEFT(A1,FIND(“/”,A1)-1)
C1 =MID(A2,LEN(B2)+2,FIND(“/”,A2,LEN(B2)+2)-LEN(B2)-2)
D1 =MID(A2,LEN(B2)+LEN(C2)+3,LEN(A2)-(LEN(B2)+LEN(C2)+2))
It looks hairy, but it works 🙂
Thanks Dee! I will give it a try.
Ok, so I’ve been digging and thinking and am going to try something new.
I created a new default template with all my sheets having all cells formatted as text. Now when I type R5 in a cell it stays R5 and doesn’t change to currency (since my system currency symbol is R for rand).
Haven’t tested much beyond that, because that was the bugbear that set of this round of excavation, but it might solve some of the other issues too.
Have a gander at http://www.techrepublic.com/blog/msoffice/modify-excels-default-sheet-to-fit-your-needs/2672 and http://office.microsoft.com/en-us/excel-help/customize-how-excel-starts-HP005199391.aspx – in particular the last item on the list of customizations.
They pertain to Excel 2003, but the procedure holds for 2010 except that the template file extension is now .xltx
Happiness is… scratching an itch 😀 I’ll keep my fingers crossed that this works for all our sakes’.
Thanks for this tip, I couldn’t hate Excel more and this saved me a lot of headache.
I was trying to find a solution since 2 days. Thanks a lot, you saved me.
umm.. to display numbers non-scifi u can just format-cells -> number -> decimal places -> 0
jaxx0rr,
The problem isn’t just how Excel displays alphanumerics, but how it treats them. If you have an alphanumeric with the letter “E” in the middle of it, it treats it as scientific notation, virtually always. How you format the cell has NO effect.
In my case, I had thousands of room numbers I was trying to sort. They were all in the same format: xxyzz where x is the floor number, y is a letter for area of the floor, and z is the room number. While excel would treat 03A24 exactly how I wanted it treated, it would treat 03E48 like scientific notation in almost all formulas. I finally found some formula commands that ONLY applied to text, and was able to get excel to do what I wanted.
Thank You.
Rich, I have teh same problem with warehouse locations: can you let me know what formulas there are that leave the “E” numbers as they were?
Thanks.
Chris,
Unfortunately I don’t remember what I did or what functions I used. I do remember that I had to use functions that ONLY operated on text. I wish I could be more help.
i had import data from DB file to csv showing scintific format, when i again import csv file to db showing scintific number but i required original number. please suggest what can i do?
The recipient column, before pasting your data into it, convert it into text format. Right click -> Format -> Text and Enter.
Now paste the data into it.
Great trick. Now if MICROSOFT IS LISTENING, please fix this with the “data only” (or similar) feature James here suggests. The very fact we have to use a trick to get Excel to do what was expected in the first place is so incredibly annoying… and I work with this type of data daily.
Turn the column to “Text” and save as Excel format.
I wouldnt call the needed feature “data only”, I would call the feature “Quit using the built-in half-assed artificial intelligence to make formatting decisions.”
THANK YOU!!! YOU SAVED MY LIFE! 😀 😉
Wow! I have been battling this for years. I can’t believe there was such a simple solution. Thanks so much!
I found a much easier solution: just go to the Formulas tab and click the “Show Formulas” button. Scientific notation is gone, leading zero is visible, and the data is saved to CSV without formatting.