Odtnhj

Problem: how to pull exactly 6 continuous figures from string

Example:

f657674
576767g
tt454656y
465767yy
x1234567
1234567x
n645856g
s-5656-54654657657-6576-46567785-4354
pof-user-201734545435454
4545665
345678
f546576767g
rtryty

it should give

657674
576767
454656
465767
(blank value)
(blank value)
645856
(blank value)
(blank value)
(blank value)
345678
(blank value)
(blank value)

What I've tried: (A1 is the cell of the first string)

=IFERROR(LOOKUP(10^6;MID(A1;ROW(INDIRECT("1:"&LEN(A1)-5));6)+0);"")

Then I drag this formula for the other rows and it gives:

 657674 (right)
 576767 (right)
 454656 (right)
 465767 (right)
 (blank value) (right) 
 (blank value) (right)
 645856 (right)
 657457 (wrong)
 435454 (wrong)
 4545665 (wrong) 
 345678 (right)
 546576767 (wrong)
 (blank value) (right)

Version: Excel 2016

Inspired by Bruce, but paired down to the minimum

Function ContainsSix(ByVal rng As Range) As String
 Dim re As RegExp
 Dim mc As MatchCollection
 Dim CellValue As Variant

 CellValue = rng.Cells(1, 1).Value2
 Set re = New RegExp
 With re
 .Pattern = "(?:D|^)(d6)(?:D|$)"
 .Global = True
 .MultiLine = True
 .IgnoreCase = True

 If .Test(CellValue) Then
 Set mc = .Execute(CellValue)
 ContainsSix = mc(0).SubMatches(0)
 End If
 End With
 Set re = Nothing
End Function

A description of the regular expression:

• Match expression but don't capture it. [D|^]
  
  
  • Select from 2 alternatives
    
    
    • Any character that is not a digit
    
    
    • Beginning of line or string
    
    
  
  


• A numbered capture group. [d6]
  
  
  • Any digit, exactly 6 repetitions
  
  


• Match expression but don't capture it. [D|$]
  
  
  • Select from 2 alternatives
    
    
    • Any character that is not a digit
    
    
    • End of line or string
    
    
  
  

You will likely want a UDF instead of a built-in function. This should work, but likely needs tweaking. Your example in your OP returns 345678 as a correct return, but there are no letters on either side. Therefore, I assume you want either: 6 consecutive numbers on their own, or 6 consecutive numbers with a letter on both sides.

Add this to the workbook module, and you can call like =return_numbers(A1). You may or may not have to add the RegEx Reference to VBEditor.

Function return_numbers(ByVal cel As Range) As String
Dim strPattern As String
Dim regEx As New RegExp

strPattern = "[a-z]d6[a-z]"

With regEx
 .Global = True
 .MultiLine = True
 .IgnoreCase = True
 .Pattern = strPattern
End With

Dim matches As Object
Set matches = regEx.Execute(cel)

If Len(cel) = 6 And IsNumeric(cel) Then
 return_numbers = cel.Value
 Set regEx = Nothing
 Exit Function
End If

If matches.Count <> 0 Then
 return_numbers = Mid(matches.Item(0), 2, Len(matches.Item(0)) - 2)
ElseIf matches.Count = 0 Then
 strPattern = "[a-z]1d6$"
 regEx.Pattern = strPattern
 Set matches = regEx.Execute(cel)
 If matches.Count <> 0 Then
 return_numbers = Mid(matches.Item(0), 2, Len(matches.Item(0)) - 1)
 ElseIf matches.Count = 0 Then
 strPattern = "^d6[a-z]1"
 regEx.Pattern = strPattern
 Set matches = regEx.Execute(cel)
 If matches.Count <> 0 Then
 return_numbers = Mid(matches.Item(0), 1, Len(matches.Item(0)) - 1)
 End If
 End If
End If

Set regEx = Nothing

End Function

_{If you're wanting to speed this up, I think if you switch the If/else statements, it might run a little quicker since it won't always run the Regex, if it finds 6 lonely digits}

Edit: This is rather clunky. I'm sure there's a better regex pattern, so please let me know.

=IFERROR(0+MID(A1,MATCH(8,MMULT(ABS(ISNUMBER(0+MID(MID("ζ"&A1&"ζ",ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-5)),8),1,2,3,4,5,6,7,8,1))-1,0,0,0,0,0,0,1),1;1;1;1;1;1;1;1),0),6),"")

Note that if you're not using an English-language-version of Excel then parts of the above may need amending.

Regards