May 26 2023 07:58 PM
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?
May 26 2023 08:17 PM
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)
May 26 2023 09:09 PM
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.
May 26 2023 09:53 PM
Solution
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
May 26 2023 10:04 PM - edited May 26 2023 10:04 PM
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
May 26 2023 10:13 PM
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.
May 26 2023 09:53 PM
Solution
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