Return last word which is not a number

February 27, 2022

I am using these two functions as I want to return the last non-numeric word in a text string. The text string has spaces as separators between numbers and text.

In isolation these functions work as expected. But when I combine them, I always get a blank result. Not sure why this happens.

 Function ReturnLastWord(The_Text As String)
    Dim stGotIt As String
    
    stGotIt = StrReverse(The_Text)
    stGotIt = Left(stGotIt, InStr(1, stGotIt, " ", vbTextCompare))
    ReturnLastWord = StrReverse(Trim(stGotIt))
    End Function
    
    Function RemoveNumbers(Txt As Variant) As String
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "[0-9]"
            RemoveNumbers = .Replace(Txt, "")
        End With
    End Function
    
    sub test
    Dim mystring as string 
    mystring = ReturnLastWord(RemoveNumbers(c_str))
    end sub

>Solution :

Last Substring With Not All Digits

Here’s a different approach.

Option Explicit

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns the last substring whose characters are not all digits.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function GetLastWord( _
    ByVal Sentence As String, _
    Optional ByVal Delimiter As String = " ") _
As String
    Dim SplitString() As String: SplitString = Split(Sentence, Delimiter)
    Dim S As String
    Dim n As Long
    For n = UBound(SplitString) To 0 Step -1
        S = SplitString(n)
        If Len(S) > 0 Then
            If Not S Like String(Len(S), "#") Then
                GetLastWord = S
                Exit Function
            End If
        End If
    Next n
End Function
    
Sub GetLastWordTEST()
    Dim MyString As String
    MyString = GetLastWord("234 asd as1 123") ' result: 'as1'
End Sub