# [SOLVED] formula for extracting numbers from string of characters

Wed, 21 Nov 2012 - 5:34 AM

eg ‘’ word wooordd w55d ‘’ : of interest here is only the number 55

Fri, 23 Nov 2012 - 4:33 AM

#2
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

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.

Read you later! Lupp from Munich