Get integer after string in VBA [duplicate]

Question

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

Is Vzm always the prefix? What to do with the second line, there is no number after VM. — TinMan
– TinMan, Commented Dec 12, 2024 at 16:52
@TinMan No VM is only for this example. the string can change but is always pulled from the value in cell B2. If no number is present then 1 should be the outcome. — Deke
– Deke, Commented Dec 12, 2024 at 16:54
What is the line PrefixString = Replace(PrefixString, "" & prefix & "", "") for ? Wouldn't .Pattern = prefix & "(\d+)" do it ? — CDP1802
– CDP1802, Commented Dec 12, 2024 at 17:28
Yes that did do it. The other line was me trying to see if it was a situation I could replace the string with the number. Obviously failing. Your suggestion totally fixed my issue. Really appreciate your help! — Deke
– Deke, Commented Dec 12, 2024 at 17:45

taller · Accepted Answer · 2024-12-12 18:20:40Z

-1

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

edited Dec 12, 2024 at 18:20

answered Dec 12, 2024 at 18:01

taller

19.7k2 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Spectral Instance Dec 12, 2024 at 18:16

55 & 22 are returning more than the first digit

taller Dec 12, 2024 at 18:23

Yes, you might be correct, the "number" in get the number after my string likely refers to a digit. In any case, the revised code offers two options.

Collectives™ on Stack Overflow

Get integer after string in VBA [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related