# Undesired Words BEFORE and AFTER the Street Name

#### GoodQuestions7

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

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
2135 Main Street

Thank you.

Last edited:

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### azumi

##### Well-known Member
maybe:

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

#### István Hirsch

##### Well-known Member
This formula worked only for the undesired words AFTER the street name.

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
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
AB
1N1-Florida # 4214 Main Street Floor 124214 Main Street
3N3-San Diego # 2135 Main Street Suite 32135 Main Street
4N1-Florida # 4214 Main Street Floor 124214 Main Street
5N1-Florida # 4214 Steel Street Floor 124214 Steel Street
Sheet

Replies
5
Views
3K
Replies
1
Views
313
Replies
2
Views
974

1,195,960
Messages
6,012,559
Members
441,710
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back