Excel: Getting rid of everything except numbers | Hacker News Reader

Excel: Getting rid of everything except numbers

excel.tips.net

45 pointsdamir32 comments

A chance to use my favourite feature of excel, flash fill:

Given:

    A       B
    a1b2c3  123
    2g34
    34f5
    4l5p6
    1.23E+06

Selecting column B with the one example cell and hitting flash fill yields:

    A         B
    a1b2c3    123
    2g34      234
    34f5      345
    4l5p6     456
    1.23E+06  12306

I'd still absolutely love to see the code behind this feature.

EDIT: i just noticed an error in the last row, should be 1230000 not 12306. Ahh well.

> "I'd still absolutely love to see the code behind this feature."

Windows PowerShell has a cmdlet ConvertFrom-String which "supports automatically-generated, example-driven parsing based on the FlashExtract, research work by Microsoft Research."[1]. Then when they open-sourced Powershell, that was one of the cmdlets which did not come over and is no longer supported, so no way to see the source code there, sadly.

(Video [2] shows that FlashFill and FlashExtract and Excel / PowerShell uses are related, and he says it generates many possible programs which fit the given examples, and uses 'machine learning based ranking techniques' to choose which one fits best).

[1] https://docs.microsoft.com/en-us/powershell/module/microsoft...

[2] https://www.youtube.com/watch?v=w-k9WjRJvIY

I think this is the paper https://arxiv.org/abs/1204.6079

Ah yes, Flash fill. It always falls at the last hurdle. Nice idea, never quite gets it right.

Switch to R1C1 Notation. It makes a lot of Excel magic easier to understand.

It looks something like:

try { return parse_float(text) } catch ParseError { return eval(match(/\d+/g, text).matches.join("")) }

Unrelated to the implementation: in what circumstances is flash fill useful?

Reminds me of one thing I hate about Excel (and Google Sheets):

There is no way to permanently disable scientific notation. I never 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.

I guess I'm confused what alternative you want, to display long numbers? Even more so than a sheet of paper, an excel doc has tiny windows to display a huge potential range of values, and something like 0.00000567 simply might not fit.

Seems like if it bugs you, just ctrl+a then set the number format, same way you might set the font and size at the start of a word doc.

Yes, display long numbers. Exactly why this is not a sheet of paper - I can resize the columns all I need.

Just tested in LibreOffice calc: 30937930503010769238 is mangled to scientific notation: 3,09379305030108E+19 . If I change the format to anything (but it's already a generic number) than it becomes 30 937 930 503 010 800 000.00 or something. And if I change the format of the cell to the text it becomes... 3,09379305030108E+19 again.

And yes it's a number, though it doesn't get used as such in my case. If it was really used for anything of value - I would be really pissed for the 30k inaccuracy.

You may be (genuinely) surprised to know that's not because of display notation cut anything off it's because IEEE double floats are being used once it gets interpreted as a number and that's as far as the smaller digits in such a large number are accurately encoded for.

https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/1...

https://ask.libreoffice.org/t/decimal-precision-how-to-have-...

I know and I don't care. Just leave my numbers as is.

It’s not just a display issue. It will silently convert any digits past 16 into zeros with no way to retrieve them.

Where i work, transaction records have an ID that is a 20 digit numeric code. If you are not very careful when importing that, pasting that, or editing cells, Excel will just strip out data because it’s priority is to convert it to a lossy format.

That has to do with the backing number type in Excel being an double precision float not what the display convention of the value is.

As Tagbert noted in their response, your suggested solution does not work.

Imagine you have an 18 digit number that happens to end in a couple zeros and Excel "helpfully" converts this to scientific notation. Ok, you say, just set the number format, except then you don't get your original number, you get something else. This is because Excel (and Google Sheets) silently convert numbers. At least in Google Sheets if you explicitly Import a sheet you can uncheck the box to convert and it will come in as plain text, but you have to do that every time.

So yes, I would prefer to just display long numbers. It's not paper and I can resize columns to fit.

So I think your complaint is really about the internal float storage truncating sig figs more than anything.

I agree that for all its "smart" pattern matching features, Excel should be able to figure out when to give you a better storage precision, and handle it fluidly in the background. Seems like we could assume one doesn't import a ton of extra digits for no reason.

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.

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.

I don't think that's quite fair. You run into that sort of problem with traditional software solutions as well, except sometimes even worse because business logic is deep in some esoteric codebase/language with all sorts of unclear dependencies. And instead of being able to ask your buddy in the accounting department to take a look, you need someone who knows Java or Python or whatever, you need a literal software engineer.

I've never been at a company that didn't have at least one Excel guru (well okay, I have. It was a tech startup!). The nice thing about Excel is that it's the same everywhere, widely used, and Well Understood, even if your particular spreadsheet isn't. Personally I would rather be handed a messy spreadsheet than a messy 5k LoC Python codebase.

You tend to find these sorts of spreadsheets everywhere though, not just massive companies but small businesses, charities, non-profits. They dont have the manpower or resources to deal effectively with complexity. Its best not to add anything too complex to to their spreadsheets, but it still happens and organisations grow to reliant on them.

Very true. It's kind of a lesser evils situation as to where you want to put the complexity. Personally I'd feel more comfortable putting it in Excel than a traditional programming language, for the above reasons. But there are situations where that doesn't make sense either.

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.

In LibreOffice, you can just use the formula

    =IF(ISNUMBER(A1), A1, NUMBERVALUE(REGEX(A1, "[^0-9]", "", "g")))

to get the equivalent of the column C to paste special from.

No need to invoke any other languages or APIs. Just use the native cell functions.

Nice! Easy enough. Still, if I didn't know the formula, running a JavaScript macro is often a quicker option for me as a JavaScript developer.

I didn't know it either. And I wouldn't call myself a LibreOffice power user. But I still don't think it was very hard to put that together in 5 minutes using informed trial-and-error, LibreOffice's built-in autocomplete, and one bit of DDG/StackOverflow to find the name of the `NUMBERVALUE()` function.

So a lot of comments here suggest copying it to another application and process it there.

Two points from my side why I would not do that:

First, you can do this directly in Excel either with a regex expression in a macro [0] or PowerQuery [1].

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

[0] https://software-solutions-online.com/vba-regex-guide/

[1] https://www.myonlinetraininghub.com/extract-letters-numbers-...

I would have loaded the spreadsheet into r and extracted the matches to a regex.

Numbers?! You kids are spoiled with your ints and your floats.

When I was young, we had nothing but ones and zeroes!

Sometimes we only had zeroes!

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?

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.

Parapgraph two. I advise you to read it:

--- start quote ---

There are a few ways you can approach this problem. Before proceeding with any solution, however, you should make sure that you aren't trying to change something that isn't really broken. For instance, you'll want to make sure that the "E" that appears in the number isn't part of the format of the number—in other words, a designation of exponentiation

--- end quote ---

Fair enough, although that's also trivially easy to account for.

I guess all Im saying is that when I see complex Excel formulas and VBA I see frustration and a ticking clock, and my preference to avoid that is to reach for simpler tools. But I appreciate that is not everyone else's default reaction. Maybe ppl see sed and run screaming...

I think the venn diagram of excel and sed users is smaller than imagined...

I think this is only true because the circle of sed users is pretty small. Excel is pretty useful for many things. I'm constantly throwing data in Excel to reason with it and graph it.

Assuming that there are many columns and I only wanted to remove the data from one I personally would find it more difficult and awkward to write a sed command to do this than the three or four lines of Python it would take (open the file in pandas, insert a new column where the values are the numbers from the old column, save as csv/xlsx).

Now watch somebody earn a $8B market cap on sed As A Service...

Reply on news.ycombinator.com