How to remove a particular word in an Excel cell?

How do you remove a particular word in a column of one cell that's in another cell in Excel.

Example: if

• A1 = *1603 Ad street New York*开发者_高级运维,
• B1 = *New York*
• then in C1 I want = *1603 Ad street*

Use the Substitute function:

=SUBSTITUTE(A1,B1,"")

EDIT: Per brettdj's suggestion, added TRIM to get rid of leading or trailing spaces:

=TRIM(SUBSTITUTE(A1,B1,""))

Just use the Replace function. leave " replace with" blank

Try this formula for range of words:

=TRIM(SUBSTITUTE(A1,INDEX($B$1:$B$56,IF(ISNA(MATCH(FALSE,ISERR(FIND($B$1:$B$56,A1)),FALSE)),0,MATCH(FALSE,ISERR(FIND($B$1:$B$56,A1)),FALSE)),1),""))