Sub get_last_word_in_string()
    Dim strAttorneyName: strAttorneyName = "My name is Smith"
    Dim arr, last_name As String
    arr = Split(strAttorneyName, " ")
    last_name = arr(UBound(arr))
End Sub

Sub get_last_word_in_string()
    Dim strAttorneyName: strAttorneyName = "My name is Smith"
    Dim last_name as String
    last_name = Right(strAttorneyName, _
        Len(strAttorneyName) - _
        InStrRev(strAttorneyName, " ", _
        Len(strAttorneyName) - 1))
End Sub