To remove text from a cell based on matching content in Excel (or replace multiple characters), use the SUBSTITUTE or MSUB functions.
This tutorial is a part of our definitive guide on Excel Formulas.
How to remove text from a cell by matching the content
Steps to remove a matching part from a string:
- Use the SUBSTITUTE function.
- Add the cell reference, for example, B2.
- Type the text that you want to remove.
- For replacement text, use an empty string (“”) to replace text by matching.
=SUBSTITUTE(source_range, "text_to_remove", "replace_with_value")
We want to remove the “*” character from the product description in this example. To remove text from a cell-based by matching content apply the SUBSTITUTE function.
Enter the following formula in B2:
=SUBSTITUTE(A2, "*", "")
The SUBSTITUTE function enables you to replace text by matching content. However, don’t forget that it’s a case-sensitive function; you must avoid mistyping.
If you want to remove more than one character from a text, you can use the “pattern” as an argument.
=SUBSTITUTE(B3, "**", "")
Remove more than one matching character
If you want to remove multiple unwanted characters from a text, you can use nested SUBSTITUTE functions.
In the example, the matching characters are: “?” and “*”
Replace multiple characters in a text string using VBA
If you want to replace multiple characters in a string, you can use a user-defined function; MSUB.
MSUB function: Syntax and arguments
- text: the cell that contains the text
- oldtext1: the character that you want to remove
- newtext1: replacement text
=MSUB(text, newtext1, oldtext1, [newtext2],[oldtext2]...)
In the example, we want to remove multiple characters in various positions.
Formula to replace the “*“,”?” and “–” characters:
If you don’t want to insert the VBA code below into a new module, you can download the Workbook that contains the MSUB function.
Public Function MSub(text As String, NewText As String, ParamArray OldText() As Variant) As String Dim vItem As Variant Dim sReturn As String Dim vArray As Variant sReturn = text vArray = OldText BubbleSortLen vArray For Each vItem In vArray sReturn = Replace(sReturn, vItem, NewText, , , vbTextCompare) Next vItem MSub = sReturn End Function Public Sub BubbleSortLen(ByRef vArray As Variant) Dim i As Long, j As Long Dim sTemp As String For i = LBound(vArray) To UBound(vArray) - 1 For j = i To UBound(vArray) If Len(vArray(j)) > Len(vArray(i)) Then sTemp = vArray(i) vArray(i) = vArray(j) vArray(j) = sTemp End If Next j Next i End Sub
Additional resources and examples