Need numbers from within cells

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

BI & Data Analysis

excel

Formulas and Functions

Macros and VBA

training

Subodh_Tiwari_sktneer

May 27, 2023

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

bsrujan

Copper Contributor

May 27, 2023

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

Subodh_Tiwari_sktneer

Silver Contributor

May 27, 2023

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.