Aug 12 2021 09:28 AM
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
Aug 12 2021 12:12 PM - edited Aug 12 2021 12:13 PM
@Geir Hogstad 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.
Aug 12 2021 02:01 PM
Aug 12 2021 02:39 PM
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.
Aug 12 2021 02:53 PM
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.
Aug 12 2021 11:53 PM
@Peter Bartholomew @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.
Aug 13 2021 02:43 AM
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?
Aug 13 2021 03:17 AM - edited Aug 13 2021 03:17 AM
@Peter Bartholomew 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.
Aug 13 2021 04:53 AM
Aug 13 2021 06:30 AM
@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)