Forum Discussion
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.
I'll kick off with
=IFERROR(LOOKUP(9.99999999999999E+307,--LEFT(A2,SEQUENCE(LEN(A2)))),"")
I'll kick off with
=IFERROR(LOOKUP(9.99999999999999E+307,--LEFT(A2,SEQUENCE(LEN(A2)))),"")
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 stringReally amazing. Thanks!
- Thanks for the input.
