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

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.
  - bsrujan
    Copper Contributor
    May 27, 2023
    Its done, tq Subodh!