I’m trying to fix some code I have in VBA to get the number after my string.
For example I have a column (Y) where has the following data sets
“VM” is my string for the Prefix and I’d like to extract the number after “VM” so that getFirstDigit = 1 (in this example).
Right now however it keeps pulling in 2 from S25 in my PrefixString.
I don’t want to just look for the last digit cause the data set can change the position but the Prefix# will always remain the same. For example it might be OBC VM1 S25.
Code:
Public Function getFirstDigit(PrefixString As String) As Integer
Dim prefix As String
prefix = ActiveSheet.Range("B2").Value
With CreateObject("VBScript.RegExp")
PrefixString = Replace(PrefixString, "" & prefix & "", "")
.Pattern = "^D*(d)"
.Global = False
If .test(PrefixString) Then
getFirstDigit = .Execute(PrefixString)(0).SubMatches(0)
Else
getFirstDigit = "1"
End If
End With
End Function
Any help would be greatly appreciated!
-D
4
An alternative method to extract numbers without using RegExp.
Microsoft documentation:
Split function
Option Explicit
Public Function getFirstDigit(ByVal sInput As String, _
Optional PrefixString As String = "VM") As Integer
Dim arr: arr = Split(UCase(sInput), UCase(PrefixString))
getFirstDigit = 1
If UBound(arr) > 0 Then
If Len(arr(1)) > 0 Then
' get all digits after VM
' getFirstDigit = Val(arr(1))
' get a digit after VM
getFirstDigit = Val(Left(arr(1), 1))
End If
End If
End Function
Sub Test()
Dim sTxt
For Each sTxt In Array("OBC S25 VM55", "PMO 08 ONS VM", "OBC VM22 S25", "NO PREFIXSTR")
Debug.Print sTxt, vbTab, getFirstDigit(sTxt)
Next
End Sub
Output
OBC S25 VM55 5
PMO 08 ONS VM 1
OBC VM22 S25 2
NO PREFIXSTR 1
2