Remove Text by Matching

Learn how to remove text from a cell-based by matching content (not position) in Excel. Use the SUBSTITUTE function or the advanced SUBSTRING and TEXTRIGHT formulas.

Universal Formula to remove text by matching

=SUBSTITUTE(source_range,"text_to_remove","replace_with_value")

Explanation

To remove text from a cell-based by matching content apply the SUBSTITUTE function.
In this example displays, the formula in B2 is:

=SUBSTITUTE(A2,"*","")

How this formula works

The SUBSTITUTE function enables you to replace text by matching content.

In this example, we want to remove the * character from the product description.

SUBSTITUTE formula to Remove text by matching

The SUBSTITUTE function works easily.

  1. As first, add the cell reference, in this case, A2.
  2. After that type the text what you want to remove, “*”
  3. Finally, for replacement text, enter a blank string “” to replace text by matching.

Don’t forget that it’s a CASE sensitive function, so; you have to avoid mistyping.

Remove text by matching using UDFs

In this example, we’ll combine the SUBSTRING and TEXTRIGHT functions.

The SUBSTRING function returns the nth element of a string, where the elements are separated by a specified separator character.

The TEXTRIGTH function returns the last word or character(s) from the source range using the specified delimiter.

For example, the input cell (A4) contains “Product*3” use the formula

=CONCAT(SubString(A4,"*"),TextRight(A4,"*"))
use TEXTRIGHT and SUBSTRING

To replace text by matching, we’ll get the left part of string using SUBSTRING: “Product,” and we’ll get the right part of the text using TEXTRIGHT: “3”.

If we combine the two extracted parts using the CONCAT function, the result will be the same.

Removing more than one unnecessary characters

If you want to remove multiple unwanted characters from a text, you can use nested SUBSTITUTE functions.

For example, to remove three different unnecessary characters (“*”,”&”,”+”) from the text, use this formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(text, "*",""),"&","","+","")

Additional resources and examples