Easy? Remove "St., Rd., or Ave" Suffix from address

[jeffcoleky]

I'm trying to remove the suffix in some addresses so that it only reads the first part of the street address.

The catch is that sometimes there is a ZIPCODE in the same field and sometimes there is not. Also, sometimes it reads "Ave, Rd. or St." and sometimes "AVENUE, ROAD, STREET".

The wording of the formula might be, "remove the last word from the cell up to the first space unless there is a number also in which case remove the last TWO"

I used this code but if there is more than one word in the address it doesn't extract them both: =LEFT($A2,FIND(" ",$A2,8)-1)

Here's the sample data:
Excel 2010

	A	B
1	Before	Desired Output
2	1585 SOUTH 15TH STREET 58928	1585 South 15th
3	1358 DIXDALE AVENUE 58218	1358 Dixdale
4	5815 LAMOND DRIVE	5815 Lamond
5	5325 HAVENTREE PLACE	5325 Haventree
6	5383 WOLF PEN TRACE 58859	5383 Wolf Pen
7	5929 BAY HARBOR DRIVE	5929 Bay Harbor
8	2527 WEST BURNETT AVENUE	2527 West Burnett
9	7985 PICKWICK LANE	7985 Pickwick
10	2815 FARNSLEY ROAD 58213	2815 Farnsley
11	2131 SADIEVILLE DRIVE 58217	2131 Sadieville
12	5788 VARBLE AVENUE 58211	5788 Varble
13	7218 PEPPERMILL DRIVE 58228	7218 Peppermill

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Is there an easy way to do this?

 

[jeffcoleky]

If your prepared to use some code , this should do the job.

[COLOR=Navy]Sub[/COLOR] MG20Jun30
[COLOR=Navy]Dim[/COLOR] Rdtype      [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] Rng         [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dn          [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Wd
[COLOR=Navy]Dim[/COLOR] Stg         [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] n           [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer[/COLOR]
Rdtype = "AVENUE/ROAD/LANE/DRIVE/PLACE/TRACE/STREET"
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
        Wd = Split(Dn, " ")
    [COLOR=Navy]For[/COLOR] n = 0 To UBound(Wd)
        [COLOR=Navy]If[/COLOR] InStr(Rdtype, Wd(n)) > 0 [COLOR=Navy]Then[/COLOR] [COLOR=Navy]Exit[/COLOR] For
            Stg = Stg & " " & Wd(n)
        [COLOR=Navy]Next[/COLOR] n
            Dn.Offset(, 1) = Mid(Stg, 2)
            Stg = ""
    [COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Mick,

Can you modify the code to include removing the Zip Code if present also please?

 

[BenMiller]

No problem Glad I could help Appreciate the feedback!

 

[jeffcoleky]

I hope those are root-beers.

 

[BenMiller]

Why, do you not like happiness?