CODEWITHSUNDEEP
openoffice.orgopenoffice-calclibreoffice
Vinay
Vinay

Reputation: 470

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.

Upvotes: 8

Views: 10770

Answers (6)

Felix Ebert
Felix Ebert

Reputation: 1161

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)

Upvotes: 8

jamesmortensen
jamesmortensen

Reputation: 34078

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

Upvotes: 0

Rodrigo Lopez Guerra
Rodrigo Lopez Guerra

Reputation: 754

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

Upvotes: 1

Andrew
Andrew

Reputation: 836

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.

Upvotes: -4

Solomon WenStorm
Solomon WenStorm

Reputation: 11

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

Upvotes: 1

Robert Ilbrink
Robert Ilbrink

Reputation: 7973

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.

Upvotes: 1

Related Questions