Forum Discussion
Solved
Search Formula help!
Help you lovely genius people. Is there a formula to check a text string in a cell and if it contains a sequence of 4 or 5 numbers in a row return the 4 or 5 digit number? For some backstory....
If you are on Excel 365 Current channel, when as variant
I'm assuming this is a macro? Won't lie, I'm a bit of a novice when it comes to macros. How would this be applied so that I'd end up with a separate column with the 4 or 5 digit numbers?
No these are excel formulas. They can be drag drop / copy or you can create a dynamic array but in this case you would need to wrap it with a BYROW( ... LAMBDA ( ... ) ) like this:
=LET(arr, A1:A13,
BYROW(arr, LAMBDA(in, LET(
numSets, TEXTSPLIT(in,TEXTSPLIT(in,SEQUENCE(10,,0),,1)),
numSetLen, LEN(numSets),
FILTER(numSets,numSetLen=MAX(numSetLen))
))))there is also some error catching that could be done like if you have 2 or more of the same length number strings or no number or only number inputs but I won't spend time on those as Sergei has provided a better solution. But wanted to address your question.