Excel VBA function to remove letters at end of string

Question

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

and have you tired anything?

PKey
– PKey

2017-05-06 08:23:54 +00:00
Commented May 6, 2017 at 8:23 — PKey
– PKey, Commented May 6, 2017 at 8:23

Moosli · Accepted Answer · 2017-05-06 08:41:10Z

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

Thank you to everyone for your prompt reply. Looks like this example is what I need exactly. However other solutions are good as well and I see already other functions were I can use them. Cheers.

Ralph · Accepted Answer · 2017-05-06 08:24:04Z

2

The following solution might be what you are looking for:

Option Explicit

Function RemLetters(MyString As String) As String

Dim i As Long

MyString = StrReverse(MyString)
For i = 1 To Len(MyString)
    If Asc(Mid(MyString, i, 1)) >= 48 And _
        Asc(Mid(MyString, i, 1)) <= 57 Then Exit For
Next i
RemLetters = Mid(StrReverse(MyString), 1, Len(MyString) - i + 1)

End Function

answered May 6, 2017 at 8:24

Ralph

9,4544 gold badges35 silver badges42 bronze badges

Comments

Robin Mackenzie · Accepted Answer · 2017-05-06 08:28:02Z

You can use a regular expression:

The pattern (\w+\d+)(\w+) says to match 2 groups. First group is some letters, followed by some numbers. Second group is some letters. Then the Replace function says to replace the original string with just the first group, ignoring the second. This leaves you with just the first group of some letters, and some numbers.

Option Explicit

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))

    Debug.Print MyString(1)
    Debug.Print MyString(2)
    Debug.Print MyString(3)

End Sub

Function RemLetters(MyString) As String
    Dim objRegex As Object
    Dim strOut As String

    Set objRegex = CreateObject("VBScript.Regexp")
    With objRegex
        .Pattern = "(\w+\d+)(\w+)"
        .Global = True
        strOut = .Replace(MyString, "$1")
    End With

    RemLetters = strOut

End Function

Kostas K. · Accepted Answer · 2017-05-06 11:45:45Z

2

Another approach:

Public Function RemoveCharFromString(ByVal stringValue As String) As String

    Dim idx As Long, charCounter As Long
    For idx = Len(stringValue) To 1 Step -1
        If IsNumeric(Mid(stringValue, idx, 1)) Then Exit For
        charCounter = charCounter + 1
    Next idx

    RemoveCharFromString = Left(stringValue, Len(stringValue) - charCounter)
End Function

edited May 6, 2017 at 11:45

answered May 6, 2017 at 8:30

Kostas K.

8,5282 gold badges26 silver badges31 bronze badges

Comments

Subodh Tiwari sktneer · Accepted Answer · 2017-05-06 08:35:24Z

Or RegEx can be used like this also...

Function RemLetters(MyString) As String
    Dim objRegex As Object
    Dim strOut As String

    Set objRegex = CreateObject("VBScript.Regexp")
    With objRegex
        .Pattern = "\w+\d+"
        .Global = False
        If .test(MyString) Then strOut = .Execute(MyString)(0)
    End With

    RemLetters = strOut

End Function

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))

    Debug.Print MyString(1)
    Debug.Print MyString(2)
    Debug.Print MyString(3)

End Sub

user27357549 · Accepted Answer · 2024-09-17 17:04:02Z

0

TEXT= left(TEXT, Len(TEXT) - 4)

In this case it will remove the last four charaters from the string in VBA

answered Sep 17, 2024 at 17:04

user27357549

1

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Excel VBA function to remove letters at end of string

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

1 Comment

Linked

Related