SOLVED

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

Contributor

If I have some text like this:

 

TextResult

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.

2 Replies
best response confirmed by flexyourdata (Contributor)
Solution

@flexyourdata 

I'll kick off with

 

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

@Hans Vogelaar 

 

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

 

flexyourdata_0-1658004041613.png

 

 

Really amazing. Thanks!