I'm attempting to extract the Street Names from an Address.

For Example, if given "123 Very Big Street" I'd want to extract "Very Big".

If given, "123 Very Big" I'd want the same answer.

I've written code to do this, with a simple IF statement to identify mathcing last words like "Street" and "Drive".

How can I efficiently do this with a large list of last words? I'm drawing a blank on how to query against an array that would contain all these values.

Thanks,

Shred

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

mystr = Cells(ActiveCell.Rows.Row, 1).Value
firstblank = InStr(mystr, " ")
lastblank = InStrRev(mystr, " ")

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

If firstblank = lastblank Then
    mynewstr = mystr
Else
    If LCase(lastword) = "drive" Or LCase(lastword) = "street" Then
        mynewstr = Mid(mystr, firstblank + 1, lastblank - firstblank - 1)
    Else
        mynewstr = Right(mystr, Len(mystr) - firstblank)
    End If
End If

MsgBox mystr & vbCrLf & vbCrLf & mynewstr
End Sub