Remove text by matching

Remove text by matching

Generic formula 

=SUBSTITUTE(B6,text_to_remove,"")

Related formulas 

Remove text by position

Remove leading and trailing spaces from text

Explanation

To remove text from a cell based by matching content (not location), you can use the SUBSTITUTE function.

In the example shown, the formula in C6 is:

=SUBSTITUTE(B6,"-","")

How this formula works

The SUBSTITUTE function lets you replace text by matching content.

In this case, we want to remove hyphens from telephone numbers. The SUBSTITUTE function can handle this easily — we just need to provide a cell reference (B6), the text to remove ("-"), and the an empty string ("") for replacement text.

SUBSTITUTE will replace all instances of "-" with nothing.

Note that SUBSTITUTE is a case-sensitive function.

Removing more than one thing

If you need to remove more than one thing, you can nest multiple SUBSTITUTE functions. For example, to remove square brackets from text, you can use:

=SUBSTITUTE(SUBSTITUTE(text, "[", ""), "]", "")

You can nest at several levels, as described in this more complex formula to clean up telephone numbers.

Remove text by matching

Generic formula 

=SUBSTITUTE(B6,text_to_remove,"")

Related formulas 

Remove text by position

Remove leading and trailing spaces from text

Explanation

To remove text from a cell based by matching content (not location), you can use the SUBSTITUTE function.

In the example shown, the formula in C6 is:

=SUBSTITUTE(B6,"-","")

How this formula works

The SUBSTITUTE function lets you replace text by matching content.

In this case, we want to remove hyphens from telephone numbers. The SUBSTITUTE function can handle this easily — we just need to provide a cell reference (B6), the text to remove ("-"), and the an empty string ("") for replacement text.

SUBSTITUTE will replace all instances of "-" with nothing.

Note that SUBSTITUTE is a case-sensitive function.

Removing more than one thing

If you need to remove more than one thing, you can nest multiple SUBSTITUTE functions. For example, to remove square brackets from text, you can use:

=SUBSTITUTE(SUBSTITUTE(text, "[", ""), "]", "")

You can nest at several levels, as described in this more complex formula to clean up telephone numbers.