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.
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.
REGEX
function arguments:Arguments are separated by a semicolon ;
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."[^[:digit:]]"
: Match every character which is not a decimal digit. See also list of regular expressions in LibreOffice
[]
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""
: replace matching characters (every non-digit) with nothing = remove them"g"
: replace all matches (don't stop after the first non-digit character)