Return last word which is not a number

user_2092236 0
Viewed 43

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
vba excel
2 Answers

When the second function replaces a number, the space before it remains in the string. If the word to be returned is the last, the string remains with an empty space. Using the first function, the space is the last string character.

In order to make it return as you need, you should trim the string in the previous function:

Function RemoveNumbers(Txt As Variant) As String
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "[0-9]"
            RemoveNumbers = Trim(.Replace(Txt, ""))
        End With
End Function

Now, your test Sub will return correctly even for the last word being numeric...

user_22333080

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
user_98140690
Related