Page 1 of 1

Extract numbers from string

Posted: Wed Dec 05, 2018 7:55 pm
by mackay@otenet.gr
Openoffice 4.1.2 extract all numbers from strings starting A1to A940 to in order

Being a geriatric and new to Ooo, I need help from a Techie. I have a program without an alphabetical index which I need. I have managed to manipulate one but now the item string has both the item and page number combined eg<Convert to slab 919>. How do I extract the 919 to another column. The string is of variable length & pages 1 to 4 digits. Prefer formula as to insert as I don't know Java at all and it needs to be simple for me to understand. Have seen in forum but only for gives 1 digit.
Regards & thanks from Corfu.
AGM

Re: Extract numbers from string

Posted: Wed Dec 05, 2018 8:32 pm
by Villeroy
extract_digits.ods
(17.8 KiB) Downloaded 207 times

Re: Extract numbers from string

Posted: Thu Dec 06, 2018 11:44 am
by Lupp
The good news:
You can do it (for a string in cell A2) with the single-cell formula

Code: Select all

=REGEX($A2;"[0-9]+")
This assuming you can assure the number you want to get is the first or only one in the string.

Code: Select all

=REGEX($A6;"[0-9]+(?=[^0-9]*$)")
would get the last number contained.

The bad news:
You need to wait for LibreOffice V6.2.0 announced to be released in 2019, week 5 (beginning of february). Current state: Beta1.

If your "<...>" is just a kind of quoting, and the number to find is actually the end of your string, you can get it by

Code: Select all

=MID($A2;SEARCH("[0-9]+[^0-9]*$";$A2);999)
and this should also work in any version of AOO with "Enable regular expressions in formulae" set.
Of course, you can easily clip a fix number of trailing non-numeric characters.