Forum Discussion
Find 7# digits in a string
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)- There is definitely confusion on what is wanted here. I also thought it was exactly 7 digit result based on the sample data column labelled "Only 7 digit #" and seeing items #1 and 3 left justified as they were 7 digits long and item #4 right justified as it was >7 digits.
But now looking at the "desired output" column again I note that item #2 was completely blank so maybe the OP wants #1,3,4. So maybe the intent is 7 digits together in a string but that is a problem because #4 'desired output' is NOT 7 digits long. So is the desired output a group of numbers at least 7 digits long?
