extract number from cell in openoffice calc

Question

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.

Answer 1

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)

Answer 2

Unfortunately Libre-Office only supports regex in find/replace and in search. If this is a once-only deal, I would copy column A to column to B, then use [data] [text to columns] in B and use the - as a separator, leaving you with all the text in column B and the numbers in column C.

Alternatively, you could use =Right(A1,find("-",A1,1)+1) in column B, then sum Column C.

Answer 3

I think that this is not exactly what do you want, but maybe it can help you or others.

It is all about substring (in Calc called [MID][1] function):

First: Choose your cell (for example with "abc-23" content).

Secondly: Enter the start length ("british" --> start length 4 = tish).

After that: To print all remaining text, you can use the [LEN][2] function (known as length) with your cell ("abc-23") in parameter. Code now looks like this:

D15="abc-23"
=MID(D15; 5; LEN(D15))

And the output is: 23

When you edit numbers (in this example 23), no problem. However, if you change anything before (text "abc-"), the algorithm collapses because the start length is defined to "5".

Answer 4

Paste the string in a cell, open search and replace dialog (ctrl + f) extended search option mark regular expression search for ([\s,0-9])([^0-9\s])+ and replace it with $1

adjust regex to your needs