Find 7# digits in a string

[Geir Hogstad]

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

[#2]

@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.

https://www.myonlinetraininghub.com/extract-letters-numbers-symbols-from-strings-in-power-query-with... 

[#3]

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,""))

[#4]

@Geir Hogstad 

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.

[#5]

@Geir Hogstad 

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.

[#6]

@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.

[#7]

@Riny_van_Eekelen 

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?

[#8]

@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.

[#9]

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?

[#10]

@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)