Forum Discussion

bsrujan's avatar
bsrujan
Copper Contributor
May 27, 2023
Solved

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

 

 

BI & Data Analysis
excel
Formulas and Functions
Macros and VBA
training
  • Subodh_Tiwari_sktneer's avatar
    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
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    May 27, 2023

    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)

     

     

    • bsrujan's avatar
      bsrujan
      Copper Contributor
      May 27, 2023

      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.

       

       

      • Subodh_Tiwari_sktneer's avatar
        Subodh_Tiwari_sktneer
        Silver Contributor
        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

Resources