Excel VBA function to remove letters at end of string

excel, vba

Solution

I Testet this solution and it works. It looks always at the last Char and looks if it is a number or Not. If not it cut off the last Char and so on.

Sub Example()
    Dim MyString() As String

ReDim Preserve MyString(3)

MyString(1) = "ABC345A"
MyString(2) = "DEFG6789BC"
MyString(3) = "AHIL2431LTR"

MyString(1) = RemLetters(MyString(1))
MyString(2) = RemLetters(MyString(2))
MyString(3) = RemLetters(MyString(3))

End Sub

Function RemLetters(MyString As String) As String
Dim bolExit As Boolean
bolExit = True
Do While bolExit = True
    Select Case Asc(Right$(MyString, 1))
            Case 65 To 90, 97 To 122
                'IsLetter = True
                MyString = Left$(MyString, Len(MyString) - 1)
            Case Else
                'IsLetter = False

                bolExit = False
        End Select
Loop
RemLetters = MyString
End Function

Problem

VBA experts, I'm looking to build a function which will remove last letters [A-Z] from a string variable. For example: ``` Sub Example() Dim MyString() as string ... ReDim Preserve MyString(3) MyString(1) = "ABC345A" MyString(2) = "DEFG6789BC" MyString(3) = "AHIL2431LTR" MyString(1) = RemLetters(MyString(1)) MyString(2) = RemLetters(MyString(2)) MyString(3) = RemLetters(MyString(3)) ... ... End Sub Function RemLetters(MyString) ??? End Function ``` ... I'm expecting function to return: ``` MyString(1) = "ABC345" MyString(2) = "DEFG6789" MyString(3) = "AHIL2431" ``` So all the letters up to first number should be removed... Cheers, Andy

Original source