Stop Excel from changing large "numbers" (actually text values) to scientific notation.
Prevent Excel from changing large "numbers" (actually text values) to scientific notation.
Thanks very much for your votes. And to those who took the time to fill in the survey, thank you! This is a brief update to let y’all know that we’ve started work on this feature request.
- Urmi [Msft]
don't talk to MS about doing things right. they have everyone believing a megabyte is 1024 kilobytes...
Why does that not surprise me!!! If MS wants to support CSV, they really ought to do it right. Thanks for trying.
Double Quotes in - Format as Text
Formatted as Text - Double Quotes out
Should be simple. Lets get it done.
Just tried that, out of some interest, eh? Edited the CSV file, saved it with NotePad, opened in Excel and it completely ignored them.
If only Excel would save Text Formatted fields with double quotes around them in the CSV file (a csv standard), then the problem would be solved.
If only... sigh...
For this issue, A. Nonny Mouse, I maintain an .xlsx/.xlsb file for my main file with edits. And I use either formatting of the cell (number, with 0 digits) or Text to Columns to force all the digits to show in the cells in the Excel formatted file. (Since our issue is with ISBN's, we can handle text.) So, I edit the excel file. Save it.
Then... I export as a .csv file. Then the data is stored properly in the .csv.. This sucks as you always have to go back to your Excel to make the edits, then save it again as a .csv.
The trick is that the export to CSV saves what is _visually_ displayed when it exports. Regardless of field type.
When you open the CSV generated from an Excel file, a long saved number will _look_ like scientific notation again. But, if it was saved as the full number, that full number will still be there, behind the format.
And so you just have to click a cell to prove it to yourself that the full number is there. And you can reformat the column also to prove it.
If you have something using those cells for calculation, it won't matter about the visual format on _opening_ the CSV as long as the value is stored in the CSV itself.
Thanks so much to Ed and Jon for their responses. Unfortunately I am not an excel whiz, so am not familiar with Access. It must be in a csv file for the secondary program to run. I tried saving in excel, then converting back to csv, but the csv file switched back to the scientific formula.
I will check into Access and see if I can work with that.
Thanks again and I really appreciate your efforts
Ed's correct. Excel always sees a .csv as a raw file and reinterprets it again all over again. .xlsx or .xlsb is the way to go. You can use "Text to Columns" to force the column to a text value, so that it will display properly in pivots too.
Another option, if it must be in a .csv file, is to use Access and then Excel. The method is to do an linked file to the .csv. Define the field as text in Access. Then use a linked table to the external data source in Excel out to the Access table. It's a bit crazy, but it should work.
Also, please fix this Microsoft. It can just be a simple setting. Or even a registry setting. We are a publisher, and our entire company is affected because of ISBN's.
Ed Hansberry commented
Sounds like you aren't saving it as an *.XLSX file. If you save it back as a CSV, formatting is not saved, only data. I've never seen that happen (lose its format preferences) with a true Excel file.
This works as long as I am in the file, but once I save, exit and reopen the file, it converts back to the scientific formula. Any ideas on how to help with that? Thanks!!
Luis Quezada commented
Any updates Microsoft? Issue has been worked on for a year almost.
Pat Babcock commented
@Ccis Bmkh: Yes,yes - we all saw your brilliant YouTube the first time you posted it earlier the same day. We're all thoroughly amazed by your brilliance, and how you stumbled upon a concept absolutely none of us could possibly have conceived of- particularly since we crave a behavior that allows numbers to remain numbers. You can stop now. Thanks.
Ccis Bmkh commented
NO MORE E+ SINTIFIC NOTATION
Pat Babcock commented
@Anonyzak spake thusly: "i'm sure there are some statistician/scientists who DO use Scientific Notation"
Excel is the "Engineer's word processor". Statistically, there are huge numbers of engineers using Excel, and we are pretty much all adherents to Scientific Notation by nature of the data we play with. HOWEVER, I'm also one of the early respondents to this thread: Excel should not convert numbers on import - especially since, in converting to SciNot, they drop significant digits. The number import (and entry) behavior should be user-selectable, both globally and in the import dialog... And as one of those respondents back ... What? Four years ago, now? ... I am equally frustrated that this seems to have been going absolutely nowhere at a very high rate of speed. It's almost as if UserVoice is for input to politicians...
@Anonymous: i'm sure there are some statistician/scientists who DO use Scientific Notation, however, statistically speaking, think of the number of them compared to the rest of the world.
it is probably right on par with the @ProfessorMickelson estimate.
I wonder - if Excel did away with scientific notation completely, how many people would notice?
I suspect that Excel has this "feature" to convert large strings to scientific notation, that hardly anyone at all would want to use.
@Ccis Bmkh yes we know. You can also put a single quote ' in front of the digits to make Excel treat it as text format. The issue is that we should not have to do anything in Excel to stop the scientific notation since it is far less often that scientific notation is wanted.
Anonymous @ProfessorMickelson estimate is probably correct. :)
Ccis Bmkh commented
i tried every solution on forums but no use untill i found this on youtube, it worked 100 %
Probably 1.0E-6% of the users ;)
Leigh McDonald commented
An excellent question. This "feature" is a major problem for a huge portion of your user base (speaking as a publisher, I can tell you that dealing with metadata in Excel is required by the industry and 13 digit ISBNs are a total nightmare because of this one issue.)
I'm having a hard time imagining why ANYONE would want a field that claims "The cell is displayed exactly as entered" to *actually* convert numbers to a completely different and very specific format that is useless for most applications?