Forum Discussion

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

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

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