Forum Discussion
Find 7# digits in a string
Moving into beta release
= MAP(comment,
LAMBDA(string,
LET(
k, SEQUENCE(1+LEN(string)),
chr, --MID(string,k,1),
nonNum, FILTER(k, ISERROR(chr)),
freq, FREQUENCY(k, nonNum)-1,
strt, XMATCH(7, freq),
res, MID(string,strt,7),
IFERROR(res,"")
)
)
)will return the result as a single array formula over the comment array.
PeterBartholomew1 mtarler Quite interesting to see both your solutions with MAP/LAMBDA and LET. So I inserted them into the file to see how they work and learn from it. Neither of them seem to deliver the desired output as they leave out item number 4. A simple PQ solution, however, does what is asked for. See attached, Forgive my ignorance if I'm wrong.
Hi, as usual it is the developer, not Excel, that is the problem!
I interpreted the requirement as returning a string of numbers only if it has exactly 7 digits. That would be much the same as you would get if you omitted the 'Max7' step.
PQ has a definite advantage of fewer steps, though. Are the statements you use directly available from the GUI or have you drawn upon your knowledge of the M language?
PeterBartholomew1 I combined the first two "standard GUI" steps (Source and Promote Headers) into one, just for the fact that it's possible. And I wanted to make it shorter.🙂
The SelectNumbers and Exact7 steps use custom columns and require some M coding. But nothing fancy, I dare saying. And my knowledge of M is limited, but Google is great in that aspect, as most problems have been solved already and are documented by somebody, somewhere.
Riny_van_Eekelen so here is a version 2 that will pull any 7+digit number embedded inside a text cell. There is still a question of what to do if there is more than 1 7-digit number string inside that cell as I showed in Item 5. The current LET version will return everything from start of the first instance to the end of the second instance but that could be changed but this was less complicated.
=LET(in,B3, l,LEN(B3), seq,SEQUENCE(,l), foundAt,IF(ISNUMBER(--MID(in&"x",seq,7)),seq," "), start,IF(MIN(foundAt),MIN(foundAt),l+1), out,MID(in,start,MAX(foundAt,start)+7-start), out)
