SOLVED

Iron Contributor

What is the simplest formula to return leading digits from a text string?

If I have some text like this:

 Text Result 100g of bananas were found in a pool of 120l of liquified banana 100 A banana, standing alone in a field (nothing) They searched the rubble and only found 18 bananas (nothing) 15 was the number of bananas assigned to the 12 brigades 15

i.e. some text where there may be numbers at the beginning, in the middle, at the end, any combination of those three, or no numbers.

What is the simplest formula to return only numbers from the beginning of a string (if they exist) and return an empty string if there are no numbers at the beginning?

I'd like to avoid volatile functions if possible.

3 Replies
best response confirmed by flexyourdata (Iron Contributor)
Solution

Re: What is the simplest formula to return leading digits from a text string?

I'll kick off with

=IFERROR(LOOKUP(9.99999999999999E+307,--LEFT(A2,SEQUENCE(LEN(A2)))),"")

Re: What is the simplest formula to return leading digits from a text string?

Ingenious!

It would never have occurred to me to do it that way.

I had to step through it to understand it.

For others, in case it's of interest:

- LEFT(text,SEQUENCE(LEN(text))) creates an array of iteratively one-character longer text substrings from the text
- double-negative on that array converts all non-numbers to errors
- In case the lookup value is not found, the array form of LOOKUP defaults to the largest value less than or equal to the lookup value, and since you've used the largest value possible in Excel as the lookup value, it will always find the value in the --(LEFT...etc) array with the most digits, which will effectively be the longest substring of numbers at the beginning of the string

Really amazing. Thanks!

Re: What is the simplest formula to return leading digits from a text string?

Thanks for the input.
1 best response

Accepted Solutions
best response confirmed by flexyourdata (Iron Contributor)
Solution

Re: What is the simplest formula to return leading digits from a text string?

I'll kick off with

=IFERROR(LOOKUP(9.99999999999999E+307,--LEFT(A2,SEQUENCE(LEN(A2)))),"")