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