Макрос выборки

[Hugo121]

Sub tt()
    Dim a, el, c As Range
    a = Array(" вул. ", " ул. ", " просп. ", " пер. ", " бул. ", " бульвар ", " наб. ", " д.")
    With ActiveSheet.UsedRange.Columns(2)
        For Each el In a
            .Replace What:=el, Replacement:="|", LookAt:=xlPart
        Next
        For Each c In .Cells
            If InStr(c, "|") Then a = Split(c, "|"): c = a(UBound(a) - 1)
        Next
    End With
End Sub