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]
Come on Microsoft, no updated in over 2 years??!!
This is so frustrating, nobody to be able to ask "What is the deal"?
This is yet another depressing update on how votes from users really do nothing.
Do we have any ETA on this colossal generational project? I understand MS have to deal with this all by itself without the support of UN, NASA or Mr.Robot, but a rough estimate, give or take decade, would still be welcome every now and then.
There's a perfectly rational reason why it's taken MS more than two years to fix the problem. They're using Excel to code their program and it keeps converting their code into non-useable gibberish causing endless hours of re-coding. It's not like we can relate to that, is it!
A little sarcasm goes a long way. Smile a little and have a great day.
I was almost about to get all verklempt until I remembered they’ve been “working” on this for years now. Oh well, it’s not that big a deal I guess. I mean it’s not like I’m always pressed for time or anything. Besides, I really enjoy taking this time out of my day to do repetitive, dull tasks like this...before I even get to work on the stuff I’m supposed to be doing.🙄
Leigh McDonald commented
@Jes agreed 100%. I thought of you all when Microsoft crashed on Monday...I thought maybe our collective rage finally broke them.
Would it be appropriate to send a LinkedIn message asking for a status update?
This *might* be the "Urmi" from this post:
Urmi (Msft Admin) posted on July 23, 2018 that they have begun work on this requested change. As of today, there have been 532 days work days since then. That means 21,280 hours of work days have passed since they "started". I am quite certain that a few unpaid interns from a local college could have completed this several times over with less hours.
Jes Hunsballe commented
I'll never regret I voted for this and get every update from this thread. It's like the gift that keeps on giving. Every now and then another frustrated soul out there needs to vent :D
It is rather silly that I cannot have a part id from a billing report = 557E67 without losing the initial number. Why can't there be a default that allows newly created excel documents/ (.csv)'s to not have automatic formatting? Every time I run a report and have an output as a .csv I have to deal with all of the ids that have an E in the middle turn into scientific notation and lose the information that was there. The length of these are never greater than 15 characters.
Blarvis Munkel commented
I hope every single developer for the Microsoft Office product line gets fired.
This is ridiculous. If Microsoft is not going to fix this, then they should have declined the feature request instead of blatantly lying.
Coming up on the 5th anniversary of the submission of this request, just observed the 2nd anniversary of it being started.
I am a computer programmer (business systems) and if I took this much time to finish this type of task, I believe I would be fired.
Does anyone want to take a guess as to the number of people that have to spend some time fixing data coming into Excel (ling numeric strings) compared to the number of people saying "Yay, automatic scientific notation. That'll save me a step."
Is it 99% on fixing data. Or is it even higher?
zak again commented
Leigh McDonald commented
@Jim I vote you group spokesman. [**munches donut and screams into the void**]
zak again commented
lol. too true (sadly).
@Karen Grube. I'm afraid you thought this was a 'user voice' forum. This is group therapy for people who have been abused by Microsoft. There's coffee and donuts in the back. Please pull up a chair, and tell us how excel has hurt you. If you have multiple email addresses, feel free to use the 'Vote' button as much as you like. It doesn't do anything, but we find that it tends to make new group members feel better until they're ready to accept that Microsoft really doesn't care about its users or their problems. But we do. We're here. Please share.
@Karen Grube ... Can you try "Cast" or something similar on the SQL side? For me, for many Excel spreadsheets going into Access... Access tries to guess what Excel is doing and doesn't give me the opportunity to set column types. Completely frustrating. So, my first step is to open the file in Excel, set the formatting, and do a save-as as a .csv so that Microsoft lets me set the column types manually.
Fortunately, many of my files are consistent with the same columns needing the same manipulation week after week. And so I've created Excel VBA in my personal.xlsb (modified from a recorded macro of me doing the rote task) that takes the file, converts the columns, and does a save-as as the .csv.
Here's a script I use to fix date columns where the Oracle output is given in Excel through a confluence server, but the format of the dates is different in multiple columns and the Access import for csv's can't handle that. I'm also copying my code which I attach as a Macro to CTRL-T which coverts the entire column selected to Text.
'I like to set this to "Control-T" in the Macro dialog box.
Selection.TextToColumns Destination:=Selection, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 2) _
Selection.TextToColumns Destination:=Range("Z1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 5), TrailingMinusNumbers:=True
Selection.NumberFormat = "[$-en-US]d-mmm-yyyy;@"
ActiveWorkbook.SaveAs Filename:="C:\foo.csv", _
Sorry, one last thing for Karen Grube:
It is not ANY kind of problem that your source is an Excel file itself.
The other simple fix is harder to describe in a place like this, but you set up a Power Query for the source file. You manipulate the column to get the actual numerals displayed, then save the Query.
And load it into the spreadsheet, of course.
Once set up, it is there forever, unless you delete it. You can save the spreadsheet empty of data and then loading the new files you receive is as simple as opening it and reloading the data. (Excel will even prompt you that the data might be newer and you should refresh.)
Power Query saved all your data mods so each time it refreshes, it will do the same steps to the data file before loading the data. That means "One and DONE" — set this up adequately once, and you never need do more than refresh.
And that's less work than anything.
You have SQL skills so I have no fear you can navigate the few simple Power Query steps needed. None at all. It even uses SQL for this work and its process can be directly edited by someone SQL-savvy.
One and DONE.