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?

 

[kpark91]

The most stable way would be to place words you want to truncate.
For example, "Street", "Avenue"...

Then the formula should look through these words and check with the full address and then remove all the characters after the found word.

However, this may cause a bug if your full address is something like
"1234 Street Lane 11859"

 

[jeffcoleky]

Isn't there a way to search fromt he right for a number, and if found remove it and the word before it, if not found remove the last word?

 

[BenMiller]

Probably a WAAAAY shorter way to do it, but this should do the trick -

=IF(ISNUMBER(--RIGHT(A1,5)),LEFT(A1,FIND("?",SUBSTITUTE(A1," ","?",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))-1),LEFT(A1,FIND("?",SUBSTITUTE(A1," ","?",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1))

 

[MickG]

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

 

[jeffcoleky]

Probably a WAAAAY shorter way to do it, but this should do the trick -

=IF(ISNUMBER(--RIGHT(A1,5)),LEFT(A1,FIND("?",SUBSTITUTE(A1," ","?",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))-1),LEFT(A1,FIND("?",SUBSTITUTE(A1," ","?",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1))

I appreciate all the help from everyone. However, this formula is the closest I've come to a solution. Besides the fact that the formula doesn't work, I'm embedding this formula inside a Vlookup and ISNA formula so a LONG formula is less desirable.

I'd LIKE to avoid code, but will resort to MickG's method if I absolutly have to.

Can anyone take this long one and help me make it work but shorter?

=IF(ISNUMBER(--RIGHT(A1,5)),LEFT(A1,FIND("?",SUBSTITUTE(A1,"  ","?",LEN(A1)-LEN(SUBSTITUTE(A1,"  ",""))-1))-1),LEFT(A1,FIND("?",SUBSTITUTE(A1,"  ","?",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1))

 

[BenMiller]

Shortened it a bit: =LEFT(A1,FIND("?",SUBSTITUTE(A1," ","?",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-CHOOSE(--ISNUMBER(--RIGHT(A1,5))+1,0,1)))-1)

Also, the reason the formula didn't work for you is that somehow when you copied it all the " " in the formula because " " (two spaces) - change it back or copy the original one I posted (or the new one) and you should be good

 

[jeffcoleky]

Shortened it a bit: =LEFT(A1,FIND("?",SUBSTITUTE(A1," ","?",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-CHOOSE(--ISNUMBER(--RIGHT(A1,5))+1,0,1)))-1)

Also, the reason the formula didn't work for you is that somehow when you copied it all the " " in the formula because " " (two spaces) - change it back or copy the original one I posted (or the new one) and you should be good

That code is great and works on all the addresses except for "2527 West Burnett Avenue". Any idea why?

<tbody>
</tbody>

 

[BenMiller]

Works for me .. make sure you don't have a trailing space at the end of the cell. Either delete it, or you can change the formula to =LEFT(A1,FIND("?",SUBSTITUTE(A1," ","?",LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))-CHOOSE(--ISNUMBER(--RIGHT(A1,5))+1,0,1)))-1)

 

[jeffcoleky]

Works for me .. make sure you don't have a trailing space at the end of the cell. Either delete it, or you can change the formula to =LEFT(A1,FIND("?",SUBSTITUTE(A1," ","?",LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))-CHOOSE(--ISNUMBER(--RIGHT(A1,5))+1,0,1)))-1)

That modified version worked. Thanks!