Jul 16 2022 12:24 PM - edited Jul 16 2022 12:28 PM
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.
Jul 16 2022 01:03 PM
SolutionI'll kick off with
=IFERROR(LOOKUP(9.99999999999999E+307,--LEFT(A2,SEQUENCE(LEN(A2)))),"")
Jul 16 2022 01:41 PM
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!
Jul 31 2023 03:20 PM
Jul 16 2022 01:03 PM
SolutionI'll kick off with
=IFERROR(LOOKUP(9.99999999999999E+307,--LEFT(A2,SEQUENCE(LEN(A2)))),"")