Extract Exact 6 Digit Number From String

**tt388** · 02-11-2013, 12:33 PM

Hi,

I have a data set with combination of alphabets and numbers in Column A. I would like to find a formula or vb codes that can extract the string with exact 6 digital in column B. For example

TestData 123456 TestTest ---> Return 123456
TestData 123456789 TestTest ---> Return Blank (nothing)
TestData 244123456 TestTest 987654 ---> Return 987654

Any assistance is appreciated.

Thanks.

**StephenR** · 02-11-2013, 12:43 PM

A custom function is one way:

Function Six(v As Variant)

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\b\d{6}\b"
    If .Test(v) Then
        Six = .Execute(v)(0)
    Else
        Six = vbNullString
    End If
End With

End Function

E.g. =Six (A1)

**JosephP** · 02-11-2013, 12:43 PM

Function Get6Digits(r As String)
   Dim matches
   With CreateObject("vbscript.regexp")
      .Pattern = "\b\d{6}\b"
      .Global = True
      Set matches = .Execute(r)
      If matches.Count > 0 Then
         Get6Digits = matches(0)
      Else
         Get6Digits = ""
      End If
   End With
End Function

usage:
=Get6Digits(A1)
for instance