Need numbers from within cells

[bsrujan]

Hi team, 

I have a dataset which is blend of all kinds of data types and it is really inconsistent, but 1 thing that is common is that within cells, we have 7, 8, or 9 digit numbers which I need. Heads-up, there might be rows with more or less digits as well, but we'll ignore that. 

I highlighted the data I need to extract for eg. what to do?

[#2]

@bsrujan 

You may place this UDF on a standard module like Module1 and then use it like a regular function on the worksheet.

Function ExtractNumber(ByVal str As String) As Long
Dim Matches As Object

With CreateObject("VBScript.RegExp")
    .Global = False
    .Pattern = "\d{7,9}"
    If .test(str) Then
        Set Matches = .Execute(str)
        ExtractNumber = Matches(0)
    End If
End With
End Function

Assuming your string is in A2 then try...

=ExtractNumber(A2)

[#3]

Hi @Subodh_Tiwari_sktneer, 

Thanks for the help. I tried your code, it works.

However, it also is giving numbers for the following columns. I want to ignore these specific columns in case there isn't 7,9 (8 as well, which we missed in code) digits in a column. Lets say there are more than 9 digits in those columns.

Tq.

[#4 · Accepted solution]

@bsrujan 

Okay, please try this and see if this works for you.

Function ExtractNumber(ByVal str As String) As Variant
Dim Matches As Object

With CreateObject("VBScript.RegExp")
    .Global = False
    .Pattern = "\d+"
    If .test(str) Then
        Set Matches = .Execute(str)
        ExtractNumber = Matches(0) + 0
        If (Len(ExtractNumber) < 7) Or (Len(ExtractNumber) > 9) Then ExtractNumber = ""
    End If
End With
End Function

[#5]

Hi @Subodh_Tiwari_sktneer, 

Oh yes, this works.

Seems as additional argument with if statement to keep b/w 7&9 is what made the difference. Tq again. Still learning VBA, this will work as feedback for me. 

Have a great day ahead!

Best,

Srujan B

[#6]

You're welcome @bsrujan! Glad it worked for you. Thanks! You too.

Please take a minute to accept the proposed answer as the Best Response to mark your question as Solved.

[#7]

Its done, tq Subodh!