Search code examples
openoffice.orgopenoffice-calclibreoffice

extract number from cell in openoffice calc

I have a column in open office like this:

abc-23

abc-32

abc-1

Now, I need to get only the sum of the numbers 23, 32 and 1 using a formula and regular expressions in calc. How do I do that?

I tried 

=SUMIF(F7:F16,"([:digit:].)$")

But somehow this does not work.

Solution

  • Starting with LibreOffice 6.4, you can use the newly added REGEX function to generically extract all numbers from a cell / text using a regular expression:

    =REGEX(A1;"[^[:digit:]]";"";"g")

    Replace A1 with the cell-reference you want to extract numbers from.

    Explanation of REGEX function arguments:

    Arguments are separated by a semicolon ;

    1. A1: Value to extract numbers from. Can be a cell-reference (like A1) or a quoted text value (like "123abc"). The following regular expression will be applied to this cell / text.
    2. "[^[:digit:]]": Match every character which is not a decimal digit. See also list of regular expressions in LibreOffice
      • The outer square brackets [] encapsulate the list of characters to search for
      • ^ adds a NOT, meaning that every character not included in the search list is matched
      • [:digit:] represents any decimal digit
    3. "": replace matching characters (every non-digit) with nothing = remove them
    4. "g": replace all matches (don't stop after the first non-digit character)