Extract 2 word street names

[duteberta]

Given the cell A1 with the contents being "5519 Old Barn Dr."

What formula would get just "Old Barn"?

I have tried other formulas such as
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",200)),200,(1*(MIN(IFERROR(FIND({" NW "," NE "," SW "," SE "," N "," E "," W "," S "},A1),100))<100)+1)*200))

But all I get is "Old".

But I cannot find just 1 formula to rule satisfy most all street addresses. The above formula does not take into consideration two-word street names.

 

[lrobbo314]

If you don't mind using VBA.

EXCEL
	A	B
1	5519 Old Barn Dr.	Old Barn
2	24541 Greer River Lane	Greer River
3	13464 Purple Mustard Ct.	Purple Mustard
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=TWOWORDS(A1)

Function TWOWORDS(s As String)
With CreateObject("VBScript.RegExp")
    .Pattern = "\d+\s((\w+\s?){2})\s"
    TWOWORDS = .Execute(s)(0).submatches(0)
End With
End Function

 

[kevin9999]

If you didn't want a VBA option, then perhaps this might work? I don't know what version of Excel you have so both 365 & pre 365 options given.

Book1
	A	B	C
1	Address	365	pre 365
2	44 Wood Lane	Wood	Wood
3	5519 Old Barn Dr.	Old Barn	Old Barn
4	12 Sir Donald Bradman Drive	Sir Donald Bradman	Sir Donald Bradman
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=TEXTAFTER(TEXTBEFORE(A2," ",-1)," ",1)
C2:C4	C2	=TRIM(MID(LEFT(A2,FIND("/", SUBSTITUTE(A2," ","/", LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1),FIND(" ",A2,1)+1,100))

 

[kevin9999]

In hindsight, you don't really need the Trim:

Book1
	A	B	C
1	Address	365	pre 365
2	44 Wood Lane	Wood	Wood
3	5519 Old Barn Dr.	Old Barn	Old Barn
4	12 Sir Donald Bradman Drive	Sir Donald Bradman	Sir Donald Bradman
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=TEXTAFTER(TEXTBEFORE(A2," ",-1)," ",1)
C2:C4	C2	=MID(LEFT(A2,FIND("/", SUBSTITUTE(A2," ","/", LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1),FIND(" ",A2,1)+1,100)

 

[duteberta]

LOVE IT- thank you!

 

[kevin9999]

Glad we could help, and thanks for the feedback

 

[Peter_SSs]

LOVE IT- thank you!

Glad you got a successful outcome. It would still assist your helpers in the future if you updated your profile to show your Excel version as @kevin9999 suggested in post #3.
Click your user name at the top right of the forum, then ‘Account details’. After adjusting, don’t forget to scroll down & ‘Save’.

 

[duteberta]

Glad you got a successful outcome. It would still assist your helpers in the future if you updated your profile to show your Excel version as @kevin9999 suggested in post #3.
Click your user name at the top right of the forum, then ‘Account details’. After adjusting, don’t forget to scroll down & ‘Save’.

Ok will do now

 

[Peter_SSs]

Ok will do now

Thanks for updating.