A chance to use my favourite feature of excel, flash fill:<p>Given:<p><pre><code> A B
a1b2c3 123
2g34
34f5
4l5p6
1.23E+06
</code></pre>
Selecting column B with the one example cell and hitting flash fill yields:<p><pre><code> A B
a1b2c3 123
2g34 234
34f5 345
4l5p6 456
1.23E+06 12306
</code></pre>
I'd still absolutely love to see the code behind this feature.<p>EDIT: i just noticed an error in the last row, should be 1230000 not 12306. Ahh well.
Reminds me of one thing I hate about Excel (and Google Sheets):<p>There is no way to permanently disable scientific notation. I <i>never</i> want scientific notation. It's the 21st century and we use computers, we don't write numbers on paper and so don't need to shorten our numbers and even for people that do it should be opt-in or at the very least something that can be permanently turned off for users who will never use them.
Imagine putting that in a spreadsheet that does a key bit of work, then watching as the orginal author leaves and subsequent users have no idea how the magic works, just that it does, until it goes wrong. Which then throws a part of a organisation into complete chaos as they try to figure out a. whats going wrong and b. how to fix it.<p>They then call in IT who tell them nothing to do with us guv, we dont support your spreadsheets. Which then leads to hastily calling in a "consultant" who'll charge whatever they want to sort of fix the problem, documenting it all of course so that it can be fixed in his absence. However, people leave again, the documentation gets lost and the cycle of dealing with complex Excel spreadsheets starts again.
As an ONLYOFFICE user, I would just use the JavaScript API to write a macro that iterates through the cells and calls something like Regex.Replace(val, "[^0-9.]", "") on all the values.
So a lot of comments here suggest copying it to another application and process it there.<p>Two points from my side why I would not do that:<p>First, you can do this directly in Excel either with a regex expression in a macro [0] or PowerQuery [1].<p>Secondly, I don’t know about the authors example but generally if you have to do something like this the column is likely to be one out of many, i.e., part of a table. I can’t point my finger to it but I imagine there might be a lot of steps in that process that could go wrong (formatting issues, altering table scheme, etc.)<p>[0] <a href="https://software-solutions-online.com/vba-regex-guide/" rel="nofollow">https://software-solutions-online.com/vba-regex-guide/</a><p>[1] <a href="https://www.myonlinetraininghub.com/extract-letters-numbers-symbols-from-strings-in-power-query-with-text-select-and-text-remove" rel="nofollow">https://www.myonlinetraininghub.com/extract-letters-numbers-...</a>
Numbers?! You kids are spoiled with your ints and your floats.<p>When I was young, we had nothing but ones and zeroes!<p>Sometimes we only had zeroes!<p>I once wrote a whole database program using nothing but zeroes.
So the task is to remove all alpha chars from a column containing strings of alphanumerics?<p>I applaud the author's inventiveness to writing complex formulas and cranking out some VBA, but this is job for sed: copy the column to a text file, sed to strip out alphas, copy back, done.