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.

 

[duteberta]

Thank you all! Those are real street names from where I live in Indiana. We have an odd naming convention for our rural roads such as "E 300 N".

 

[duteberta]

Ok it's not quite working. I made to named ranges like you said and they are called "card" and "suffix". Getting some wrong results...

test2.xlsx
	A	B	C	D	E	F	G
1	Street Address	Expected Outcome	Attempt1		card		suffix
2	100 Independence Boulevard SE	Independence	Independence		N		N
3	8505 Canterbury Square E	Canturbury	Canterbury		S		N.
4	7657 Castleton Farms West	Castleton Farms	Castleton Farms		E		NE
5	8238 CR 350 W	CR 350	CR 350		W		NW
6	4116 Crooked Creek Overlook	Crooked Creek	Crooked Creek		SE		North
7	10228 Cumberland Pointe Blvd	Cumberland Pointe	Cumberland Pointe		SW		Northeast
8	5732 E 300 North	300	5732		NE		Northwest
9	3829 E SR 32	SR 32	3829		NW		S
10	6383 E Walton Dr	Walton	6383				S.
11	1917 E. State Rd 28	State Road 28	1917				SE
12	15540 Follow Dr	Follow	Follow Dr				SW
13	17147 Linda Way NW	Linda	Linda				Southeast
14	8671 N 500 W	500	8671				Southwest
15	1301 N 8th St	8th	1301				E
16	3558 North 900 W	900	3558				E.
17	739 N Raven Field Ct	Raven Field	739				East
18	7590 Sea Crest Way N	Sea Crest	7590				W
19	23050 SR37 N	SR37	23050				W.
20	6520 W 11th St	11th	6520				West
21	4455 West 126th St	126th	4455				Street
22	6643 W 300 North	300	6643				Street
23	00 W CR650 S	CR650	00				St.
24	7869 W CR650 S	CR650	7869				Drive
25	79 Wiley St	Wiley	79				Drive
26							Dr.
27							Blvd
28							Boulevard
29							Way
30							Lane
31							Ln
32							Court
33							Ct
34							Ct.
35							Ave
36							Overlook
37							Place
38							Place
39							Circle
40							Cir
Sheet1

Cell Formulas
Range	Formula
C2:C25	C2	=LET(tb,TEXTBEFORE(A2,suffix,,0,0,A2),ta,TEXTAFTER(tb,card,,,,tb),TRIM(TEXTAFTER(TRIM(ta)," ",,,,ta)))

 

[JGordon11]

Book2
	A	B	C	D	E
1	Street Address	Street		Word List
2	100 Independence Boulevard SE	Independence		Boulevard
3	8505 Canterbury Square E	Canterbury		SE
4	7657 Castleton Farms West	Castleton Farms		Square
5	8238 CR 350 W	CR 350		E
6	4116 Crooked Creek Overlook	Crooked Creek		West
7	10228 Cumberland Pointe Blvd	Cumberland Pointe		W
8				Overlook
9				Blvd
10
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=TEXTJOIN(" ",1,LET(a,TEXTSPLIT(A2," "), b, UPPER(D2:D9), DROP(FILTER(a, BYCOL(--(UPPER(a)=b), LAMBDA(x,SUM(x)=0))),,1)))

 

[duteberta]

Holy crap that is awesome. Dropping some LAMBDA! Thanks so awesome

 

[Cubist]

I'm starting to think it's not possible to get 100% of them with formula. For example, this one breaks the rules where you need the value after the suffix "Rd".
1917 E. State Rd 28 -> State Rd 28 instead of just State.

 

[Joe4]

I'm starting to think it's not possible to get 100% of them with formula.

Yes, I said that with my first reply. If you can get 95% or better, you are doing pretty good.
There are just too many variations and exceptions to be able to expect it to work perfectly in every single scenario.

For example, there are many words that you would typically want to replace that could also be valid street names, like "East Ave.", or "Court St." (two street names in my city).