Extract numbers from string

Discuss the spreadsheet application

Extract numbers from string

Postby mackay@otenet.gr » Wed Dec 05, 2018 7:55 pm

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
Ope noffice 3.2, windows XP
mackay@otenet.gr
 
Posts: 8
Joined: Fri Nov 09, 2012 10:19 am

Re: Extract numbers from string

Postby Villeroy » Wed Dec 05, 2018 8:32 pm

extract_digits.ods
(17.8 KiB) Downloaded 29 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26698
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Extract numbers from string

Postby Lupp » Thu Dec 06, 2018 11:44 am

The good news:
You can do it (for a string in cell A2) with the single-cell formula
Code: Select all   Expand viewCollapse view
=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   Expand viewCollapse view
=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   Expand viewCollapse view
=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.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2462
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 7 guests