Undesired Words BEFORE and AFTER the Street Name

[GoodQuestions7]

This formula worked only for the undesired words AFTER the street name.

=LEFT(A1,FIND(" ",A1,MIN(SEARCH({"street", "road","st ","ave","rd"}, A1&"streetroadst averd")))-1)

But what if there's like unwanted words BEFORE and AFTER the street name like this:

N1-Florida # 4214 Main Street Floor 12
N2-California # 213 Main Road Building 1
N3-San Diego # 2135 Main Street Suite 3

Is it possible to get the below output using the same pattern of the formula above?

Expected Output:

4214 Main Street
213 Main Road
2135 Main Street

Thank you.

 

[azumi]

maybe:

=TRIM(MID(LEFT(A1,FIND("[",SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-3),FIND("#",A1)+1,LEN(A1)))

 

[István Hirsch]

This formula worked only for the undesired words AFTER the street name.

=LEFT(A1,FIND(" ",A1,MIN(SEARCH({"street", "road","st ","ave","rd"}, A1&"streetroadst averd")))-1)

But what if there's like unwanted words BEFORE and AFTER the street name like this:

N1-Florida # 4214 Main Street Floor 12
N2-California # 213 Main Road Building 1
N3-San Diego # 2135 Main Street Suite 3

Is it possible to get the below output using the same pattern of the formula above?

Expected Output:

4214 Main Street
213 Main Road
2135 Main Street

Thank you.

I could not try your formula (got error) but I am afraid it would fail to delete even AFTER part, for example, for … „Steel Street”….

Give this a try:

=MID(A1,FIND("#",A1)+2,SEARCH(" ",A1,MIN(SEARCH({" street "," st "," road "," rd "," ave "},A1&" street st road rd ave ")+1))-FIND("#",A1)-2)

Excel Workbook
	A	B
1	N1-Florida # 4214 Main Street Floor 12	4214 Main Street
2	N2-California # 213 Main Road Building 1	213 Main Road
3	N3-San Diego # 2135 Main Street Suite 3	2135 Main Street
4	N1-Florida # 4214 Main Street Floor 12	4214 Main Street
5	N1-Florida # 4214 Steel Street Floor 12	4214 Steel Street
Sheet