Extract ONLY street name with no other text

[duteberta]

I've ready many articles on this but cannot find a solution for this. Is there a way to set up a table of values that a formula can reference to avoid? Such as setting up a table with terms such as "N","N. ","North", "Northeast", "Boulevard", etc to avoid when returning the result?

Sp I want to return ONLY the street name as follows and need the proper formula. It's ok if I need 2-3 helper columns to set up the proper result.

 

[Joe4]

I've ready many articles on this but cannot find a solution for this. Is there a way to set up a table of values that a formula can reference to avoid?

This is a common issue with names too, and unfortunately, there is no foolproof method. If you could could up with a method to handle 95% of your cases, you would be doing really well. But then you would still need to go through and review all the records afterwards for accuracy.

The issue is that there is just so many different possibilities to check for, and so many different variations of the same thing.
You haven't even touched on Apartment numbers in the addresses (i.e. Apartment, Apt, #, Front, Back, etc).
And the issue with letters like N, S, W, E, you have to check just for those values by themselves, and not in the middle of other words.

Here is how I might approach this, though depending upon how much data you have it could be really slow, as it involves lots of loops.
1. Create a list of all possible values you may want to exclude and store it in an Excel sheet
2. Loop through each row of data.
3. Remove everything before the first space (the number)
4. For each row of data, use the SPLIT function in VBA to separate each word into its own field.
5. Loop through each word and see if they exist in your list of excluded values (you could use a MATCH or COUNTIF function instead of looping through each word in the exclusion list)
6. If the word is not found in your list, keep it and add it to the string to return.
7. Check the next word in your split list and repeat the process
8. When done loop through all the words from the row, return the string you are left with

Not the most efficient thing, but I cannot conceive any easier way to do something like this.

 

[Joe4]

And in case it wasn't clear, this is probably going to require VBA!

 

[duteberta]

ok. I have already separated Apartments out. I've been able to whittle the results down to "E. 148th" as an example- now I just need to create a new column that gets rid of the "E. " part.

 

[Joe4]

It would be much more helpful if you posted actually data and not screen prints so we could just copy/paste your data.
I am not going to manually type them all in, but for the sake of "proof-of-concept", I added the first 6 records.
I then created an "Exclusion List". I only added the words to exclude from your 6 first records, but you will need to include EVERY SINGLE item you want to exclude in this list (which is probably going to be large and constantly growing).

So, I assumed that your data starts in cell A2, and I put the Exclusion List in column F.
Here are the results from my test (column B in red is what is being returned by the VBA code):

You can see that it matches what you wanted to return exactly (i just added "House" in as a test for any one-word entries).

Here is the VBA code behind this that runs and populates column B:

Sub GetStreetAddress()

    Dim lrEx As Long
    Dim rngEx As Range
    Dim lrAd As Long
    Dim rngAd As Range
    Dim cell As Range
    Dim sp As Long
    Dim add As String
    Dim str As String
    Dim arr() As String
    Dim i As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in exclusion list with data
    lrEx = Cells(Rows.Count, "F").End(xlUp).Row
'   Set exclusion list
    Set rngEx = Range("F2:F" & lrEx)
    
'   Find last row of addresses
    lrAd = Cells(Rows.Count, "A").End(xlUp).Row
'   Set address range
    Set rngAd = Range("A2:A" & lrAd)
    
'   Loop through all rows of data
    For Each cell In rngAd
'       Find first space in value
        sp = InStr(cell.Value, " ")
'       If space is not found, copy value over to column B
        If sp = 0 Then
            cell.Offset(0, 1).Value = cell.Value
'       Otherwise remove it and proceed with the rest
        Else
            add = Mid(cell.Value, sp + 1)
'           Split words of address into an array
            arr = Split(add, " ")
'           Initialize string we are building
            str = ""
'           Loop through each word of the array
            For i = LBound(arr) To UBound(arr)
'               See if word found in exclusion list
                If Application.WorksheetFunction.CountIf(rngEx, arr(i)) = 0 Then
'                   Add to string if not found
                    str = str + arr(i) + " "
                End If
            Next i
'           Write value one column to the right
            cell.Offset(0, 1).Value = Trim(str)
        End If
    
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

As long as your data is set up the same way, you shouldn't need to change anything in the VBA code.
If it is not, it is only the range references at the very top that would need adjusting.

 

[Cubist]

If you have both lists, you can try something like this.

Book1
	A	B	C	D	E
1	Address			Cardinal directions	Street suffix
2	100 Independence Boulevard SE	Independence		N	Boulevard
3	5732 E 300 North	300		W	St
4	15540 Follow Dr	Follow		E	Street
5	1301 N 8th St	8th		S	Dr
6	652512 W 11th St	11th		SE	Drive
7				NE	North
8
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=LET(tb,TEXTBEFORE(A2,$E$2:$E$7,,0,0,A2),ta,TEXTAFTER(tb,$D$2:$D$7,,,,tb),TRIM(TEXTAFTER(TRIM(ta)," ",,,,ta)))

 

[Joe4]

Cubist,

I was excited about a formula option. It took a few minor adjustments, but I got it to work on the first 6 rows of data, i.e.

Just note that based on his data, you will need to add directions to BOTH columns D and E.
Also, I would recommend using named ranges in those formulas for your columns D and E lists, so you don't need to adjust formulas every time the range grows (which I am sure will probably be quite often as he comes across new things he wants to exclude), i.e.

=LET(tb,TEXTBEFORE(A2,suffix,,0,0,A2),ta,TEXTAFTER(tb,card,,,,tb),TRIM(TEXTAFTER(TRIM(ta)," ",,,,ta)))

I named the ranges "card" and "suffix", but you can obviously name them however you like.

 

[Cubist]

We run into trouble when the cardinal appears before and after the street name like this one.

5732 E 300 North

 

[Scott Huish]

Using named ranges:
=LET(t,TEXTBEFORE(A2,suffix),TEXTAFTER(TEXTAFTER(t,cardinal,,,,t)," ",1))

street names.xlsx
	A	B	C	D	E
1	Address			Cardinal directions	Street suffix
2	100 Independence Boulevard SE	Independence		N	Boulevard
3	5732 E 300 North	300		W	St
4	15540 Follow Dr	Follow		E	Street
5	1301 N 8th St	8th		S	Dr
6	652512 W 11th St	11th		SE	Drive
7	408 NE San Juan Boulevard	San Juan		NE	North
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=LET(t,TEXTBEFORE(A2,suffix),TEXTAFTER(TEXTAFTER(t,cardinal,,,,t)," ",1))

Named Ranges
Name	Refers To	Cells
cardinal	=Sheet1!$D$2:$D$7	B2:B7
suffix	=Sheet1!$E$2:$E$7	B2:B7

 

[Cubist]

The proposed formulas work when the cardinal before the street names are always abbreviated.