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.

Alodium

Is the problem recurring enough to make writing a macro worth it? – Benito

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)

Jacob

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.

Murton

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

Lipstick

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

Thinking

Answer 5

I didn't figure out how to do this in OpenOffice/LibreOffice directly. After frustrations in searching online and trying various formulas, I realised my sheet was a simple CSV format, so I opened it up in vim and used vim's built-in sed-like feature to find/replace the text in vim command mode:

:%s/abc-//g

This only worked for me because there were no other columns with this matching text. If there are other columns with the same text, then the solution would be a bit more complex.

If your sheet is not a CSV, you could copy the column out to a text file and use vim to find/replace, and then paste the data back into the spreadsheet. For me, this was a lot less frustrating than trying to figure this out in LibreOffice...

Vtehsta

Well, you did not read Felix' answer, did you? It is the same algorithm in LO. You never learn something new if you don't try. ;-) – Cresting

@busybee I'll try it out when I get time. Right now, expat taxes are coming due, on top of work and other things, so getting the job done is more of a priority than fiddling. – Vtehsta

Answer 6

I won't bother with a solution without knowing if there really is interest, but, you could write a macro to do this. Extract all the numbers and then implement the sum by checking for contained numbers in the text.

Absorb