5 ms·

Excel: Getting rid of everything except numbers

45 points by damir 4y ago

kjellsbells 4y ago
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.
- CamperBob2 4y ago
  Now watch somebody earn a $8B market cap on sed As A Service...
- dmitriid 4y ago
  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 ---
  - kjellsbells 4y ago
    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...
- emehex 4y ago
  I think the venn diagram of excel and sed users is smaller than imagined...
  - ALittleLight 4y ago
    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).
CraigJPerry 4y ago
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.
- bobbylarrybobby 4y ago
  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?
- deleted 4y ago
  [deleted]
- jodrellblank 4y ago
  > "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.powershell.utility/convertfrom-string?view=powershell-5.1 https://docs.microsoft.com/en-us/powershell/module/microsoft... [2] https://www.youtube.com/watch?v=w-k9WjRJvIY https://www.youtube.com/watch?v=w-k9WjRJvIY
  - tadkar 4y ago
    I think this is the paper https://arxiv.org/abs/1204.6079 https://arxiv.org/abs/1204.6079
clircle 4y ago
I would have loaded the spreadsheet into r and extracted the matches to a regex.
yosito 4y ago
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.
- Karellen 4y ago
  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.
  - yosito 4y ago
    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.
    - Karellen 4y ago
      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.
drumhead 4y ago
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.
- extr 4y ago
  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.
  - drumhead 4y ago
    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.
    - extr 4y ago
      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.
coderintherye 4y ago
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.
- mNovak 4y ago
  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.
  - justsomehnguy 4y ago
    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.
    - zamadatix 4y ago
      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/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/ https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/1... https://ask.libreoffice.org/t/decimal-precision-how-to-have-18-decimals/33491 https://ask.libreoffice.org/t/decimal-precision-how-to-have-...
blacksqr 4y ago
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.
countmora 4y ago
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/ https://software-solutions-online.com/vba-regex-guide/ [1] https://www.myonlinetraininghub.com/extract-letters-numbers-symbols-from-strings-in-power-query-with-text-select-and-text-remove https://www.myonlinetraininghub.com/extract-letters-numbers-...

45 points by damir 4y ago

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.

CamperBob2 4y ago
Now watch somebody earn a $8B market cap on sed As A Service...
dmitriid 4y ago
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 ---
- kjellsbells 4y ago
  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...
emehex 4y ago
I think the venn diagram of excel and sed users is smaller than imagined...
- ALittleLight 4y ago
  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).

CraigJPerry 4y ago

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.

bobbylarrybobby 4y ago
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?
deleted 4y ago
[deleted]
jodrellblank 4y ago
> "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.powershell.utility/convertfrom-string?view=powershell-5.1 https://docs.microsoft.com/en-us/powershell/module/microsoft... [2] https://www.youtube.com/watch?v=w-k9WjRJvIY https://www.youtube.com/watch?v=w-k9WjRJvIY
- tadkar 4y ago
  I think this is the paper https://arxiv.org/abs/1204.6079 https://arxiv.org/abs/1204.6079

clircle 4y ago

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

yosito 4y ago

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.

Karellen 4y ago
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.
- yosito 4y ago
  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.
  - Karellen 4y ago
    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.

drumhead 4y ago

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.

extr 4y ago
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.
- drumhead 4y ago
  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.
  - extr 4y ago
    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.

coderintherye 4y ago

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.

mNovak 4y ago
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.
- justsomehnguy 4y ago
  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.
  - zamadatix 4y ago
    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/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/ https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/1... https://ask.libreoffice.org/t/decimal-precision-how-to-have-18-decimals/33491 https://ask.libreoffice.org/t/decimal-precision-how-to-have-...

blacksqr 4y ago

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.

countmora 4y ago

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/ https://software-solutions-online.com/vba-regex-guide/ [1] https://www.myonlinetraininghub.com/extract-letters-numbers-symbols-from-strings-in-power-query-with-text-select-and-text-remove https://www.myonlinetraininghub.com/extract-letters-numbers-...