String Manipulation

[omnisautnihil]

I am trying to write a very robust program for a template file I can upload for my company. I want the admin to be able to set a form for the entry values as such:

ANYTHING####

This program is to evaluate and fix the entries given by users who fail to follow the form. For example if the form says:

IVHMS34_SRS####

and the user enters: SRS140 or 720 or IVHMSSRS360

I want to fix them to say the corresponding correct form.

The problem is there are no rules. It can be any number of characters before the numbers and the numbers can be any length after the other characters. There is no deliniator that is set in stone. That form could say IVHMS_###.

I feel like the easiest way is to ask the admin for the string portion.

So my question is this: (Assuming counting from the right is the best approach) How would I find the end of the number portion to add the string portion. I know how to & things together but its finding the end of the number portion that is confusing me. (I really dont want to do a two level loop looking at each character to see if its the first non-number.)

 

[Joe4]

So, if I understand you correctly, it sounds like you could have any amount of letters followed by any amount of numbers, and you just want to extract the numeric portion.

If that is correct, a UDF such as this should work:

Function GetNumber(myEntry As String) As String
 
    Dim myLen As Long
    Dim i As Long
    Dim myStart As Long
    
    myLen = Len(myEntry)
    If myLen > 1 Then
        For i = 1 To myLen
            If Not (IsNumeric(Right(myEntry, i))) Then
                myStart = i - 1
                Exit For
            End If
            If i = myLen Then myStart = myLen
        Next i
        GetNumber = Right(myEntry, i - 1)
    End If
    
End Function

 

[Sektor]

Sub RobustProgram()

    ' Tools -> Reference -> Microsoft VBScript Regular Expressions 5.5

    Dim sPatternPart As String, sDigits As String
    Dim re As New RegExp
    Dim userInput As Variant
    
    sPatternPart = "IVHMS34_SRS" 'Control text part
    sDigits = "\d{4}" 'Control amount of digits to appear. In this case - four digits.
    re.Pattern = sPatternPart & sDigits
    
    userInput = Application.InputBox("Enter string", "String", Type:=2)
    If userInput = False Then Exit Sub
    
    If Not re.Test(userInput) Then
        MsgBox "Incorrect input", vbCritical, "Error"
        Exit Sub
    End If
    
    ' Further processing.

End Sub

 

[Sektor]

Function GetNumber() As String

    ' Tools -> Reference -> Microsoft VBScript Regular Expressions 5.5
    
    Dim sPatternPart As String, sDigits As String
    Dim re As New RegExp, mc As MatchCollection
    Dim userInput As Variant

    sPatternPart = "IVHMS34_SRS"
    sDigits = "(\d{4})" 'Catch number
    re.Pattern = sPatternPart & sDigits
    
    userInput = Application.InputBox("Enter string", "String", Type:=2)
    If userInput = False Then Exit Function
    
    If Not re.Test(userInput) Then
        MsgBox "Incorrect input", vbCritical, "Error"
        Exit Function
    End If
    
    ' Get number.
    Set mc = re.Execute(userInput)(0).SubMatches(0)
    GetNumber = mc(0).SubMatches(0)

End Function