[SOLVED] formula for extracting numbers from string of characters

2 replies [Last post]
hwkk78
Offline
Last seen: 1 year 14 weeks ago
Title: ★★
Joined: 31 Oct 2012
Posts: 13

If one has a string of text, spaces, characters and numbers and wish to extract the numbers, what are the steps to take with LOcalc?
eg ‘’ word wooordd w55d ‘’ : of interest here is only the number 55

libreoffice learner

Lupp
Offline
Last seen: 15 hours 34 min ago
Title: ★★★★★
Joined: 1 Aug 2012
Posts: 460
Parsing by spreadsheet formulas

Your question concerns parsing (See http://en.wikipedia.org/wiki/Parsing !).
In principle the more simple cases of parsing can be done by spreadsheet formulas, but the problem is non-trivial.
Every parsing needs a kind of syntax by which the acceptable strings and their relevant parts are defined.
The only instrument of describing such a syntax in short that is supported by Calc (and other spreadsheet-software) is the usage of regular expressions.
Some of the functions support regular expressions if the option is chosen. The most important one here is SEARCH.
The following assumes you are not familiar with these things. If you are: excuse me!

To be able to describe your problem more precisely and then to solve it you need studying the concept of regular expressions. There is something about this in the help-file (hope you installed it) and a lot of documents and tutorials in the web.

I now assume that you mean digits by “numbers”. If you are just interested in ONE (longest) or THE ONLY (longest) substring of your input that represents an INTEGER and there is no appearance of decimal or exponential parts. In this case a not too ambitious solution to your problem is possible by the use of regular expressions in SEARCH and with some auxiliary columns. A full parsing for numbers in different formats is much less easy. See books about formal languages and compilers for that!

A roughly designed example for the simple version you find in the attached file.

AttachmentSize
4787ParsingForNumbers001.ods 11.87 KB


Read you later! Lupp from Munich

hwkk78
Offline
Last seen: 1 year 14 weeks ago
Title: ★★
Joined: 31 Oct 2012
Posts: 13
digit extraction

Thank you.
With that I’ll be able to do exactly what I want plus learn a lot more that will be useful as I try to build this worksheet. I’m very appreciative of the format of the answer.

SOLVED

libreoffice learner

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.