how to pull exactly 6 continuous figures from string

Question

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

So, you only want six numbers returned, where there's a letter on either side? — BruceWayne, Nov 15 '18 at 19:19
yes, I want to pull only six CONTINUOUS numbers. so 565dft676 should give a blank value. But st645594uy should give 645594 — graphene, Nov 15 '18 at 19:22

BruceWayne · Answer 1 · 2018-11-15T20:55:44.843

2

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]\d{6}[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]{1}\d{6}$"
    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 = "^\d{6}[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.

edited Nov 15 '18 at 20:55

answered Nov 15 '18 at 19:37

BruceWayne

21,782
14
49
95

"Therefore, I assume you want either: 6 consecutive numbers on their own, or 6 consecutive numbers with a letter on both sides." OR it can happen one letter in one of the sides, like f565679 it should give 565679. Or 454656g it should give 454656. – graphene Nov 15 '18 at 19:44
1

@graphene - What should it return for `x1234567`? or `1234567x`? – BruceWayne Nov 15 '18 at 19:45
x1234567? or 1234567x? In both cases, a blank value. – graphene Nov 15 '18 at 19:46
@BruceWayne - The following strPattern: `(? – cybernetic.nomad Nov 15 '18 at 19:55
it throws an error: user-defined type not defined... and points out to Dim regEx As New RegExp. I suppose I need to add the regfex refernce.. – graphene Nov 15 '18 at 19:56
@cybernetic.nomad - Thanks! I tried that and get a `#VALUE` return. I suspect using your pattern, I would need to adjust the `matches.Count` part? I appreciate the regex help, that's one area I'm still pretty new to. – BruceWayne Nov 15 '18 at 20:00
@graphene - Can you update your OP with the new cases as well? This will need tweaking on the RegEx side. – BruceWayne Nov 15 '18 at 20:07
done. And really the case x123456 should give 123456 and gives a blank value with your function. If it is x1234567 it should give blank value in this case. – graphene Nov 15 '18 at 20:13
@BruceWayne - I'm familiar with Regex, but just found out Negative lookbehinds [are not supported by VBA](https://stackoverflow.com/questions/9150552/lookbehind-on-regex-for-vba#9154601) :(. if you use this pattern: `^([^\d])*?(\d{6})(?=[^\d])` and pull the rightmost 6 characters, you should be ok – cybernetic.nomad Nov 15 '18 at 20:38
@cybernetic.nomad - Hm, I must be missing something. I tried that as the only pattern and the returned values were off...I'll keep whittling down. My edit currently is a mash up of three patterns :P – BruceWayne Nov 15 '18 at 20:57

chris neilsen · Accepted Answer · 2018-11-15T21:24:52.023

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|^)(\d{6})(?:\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. [\d{6}]
- 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

score 1 · Answer 3 · answered Nov 15 '18 at 22:18

=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

how to pull exactly 6 continuous figures from string

3 Answers3