Remove Certain Last Words from String

Thanks Mikerickson for pointing me in this direction. I adapted your ideas into some code that eventually worked. I was unable to get the results I desired using ISNUMERIC when there was not a match as an error would be returned by the Match worksheet function; that worked fine if is there was a match between the lastword and the list of choices. So I resorted to ISERROR instead and got the results I needed. It now works whether there is a match or not.

I also got it working with a dynamic range pulled from the workbook ('commented line)

See code below.

Thanks again.

Private Sub CommandButton7_Click()
Dim mystr, mynewstr, lastword As String
Dim lastblank, firstblank As Long
Dim suffixarray()

mystr = Cells(ActiveCell.Rows.Row, 1).Value
firstblank = InStr(mystr, " ")
lastblank = InStrRev(mystr, " ")
suffixarray() = Array("Street", "Drive", "Lane")

lastword = Right(mystr, Len(mystr) - lastblank)

On Error Resume Next
'    If IsError(Application.WorksheetFunction.Match(lastword, Range("suffixlist"), 0)) Then
    If IsError(Application.WorksheetFunction.Match(lastword, suffixarray, 0)) Then
        mynewstr = Right(mystr, Len(mystr) - firstblank)
      Else
        mynewstr = Mid(mystr, firstblank + 1, lastblank - firstblank - 1)
    End If
On Error GoTo 0
  
  MsgBox mystr & vbCrLf & vbCrLf & mynewstr

End Sub

Correct. I was testing both methods. I prefer the array and maintaining the list in the code for this particular application.