Forum Discussion
Find 7# digits in a string
Hi,
I have text string where I would like et select numbers only from. And i manage to do that. But it find all the numbers, is it possible to only select the numbers when it is 7 digits concatenated?
Excample file is attached.
/Geir
9 Replies
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?
A somewhat roundabout formula!
= LET( k, SEQUENCE(1+LEN(@comment)), chr, --MID(@comment,k,1), nonNum, FILTER(k,ISERROR(chr)), freq, FREQUENCY(k,nonNum)-1, strt, XMATCH(7,freq), res, MID(@comment,strt,7), IFERROR(res,"") )Guess one has to start somewhere.
- Geir, you did the 'hard' part. Now just use the formula: =IF(LEN(C3)=7,C3,"") or if you want it in a single cell you should take advantage of the new LET() function like this:
=LET(in,B3,
s,--MID(in,SEQUENCE(,MAX(LEN(in))),1),
digits,CONCAT(FILTER(s,ISNUMBER(s),"")),
IF(LEN(digits)=7,digits,"")) Hogstad_Raadgivning Sounds like you need to get into PowerQuery for this kind of task. The link below explains how you can extract all numbers from a text, using Text.Select. Once that is done, you can check if the length of the string with numbers is at least 7 characters long and, if so, extract the 7 right-most characters.
