Forum Discussion
Search Formula help!
If you are on Excel 365 Current channel, when as variant
try this:
=LET(in, A1,
numSets, TEXTSPLIT(in,TEXTSPLIT(in,SEQUENCE(10,,0),,1)),
numSetLen, LEN(numSets),
FILTER(numSets,numSetLen=MAX(numSetLen)))although MAX(numSets) would be easier, it would return 999 instead of 000023
but if that isn't a concern you could try
=LET(in, I10,
numSets, TEXTSPLIT(in,TEXTSPLIT(in,SEQUENCE(10,,0),,1)),
MAX(numSets))this of course is all assuming you don't have other even larger numbers appearing in those cells/fields. If so I would go back to the above version and in the FILTER also filter to be only LEN =4 or 5
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.