Extract numbers from string

[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

[Villeroy]

extract_digits.ods

(17.8 KiB) Downloaded 209 times

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