[no_toc] 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.
The SUBSTITUTE function works easily.
- As first, add the cell reference, in this case, A2.
- After that type the text what you want to remove, “*”
- 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,"*"))
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