Remove text by matching

Remove-text-by-matching

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:

  1. Use the SUBSTITUTE function.
  2. Add the cell reference, for example, B2.
  3. Type the text that you want to remove.
  4. For replacement text, use an empty string (“”) to replace text by matching.

Formula

=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-character-from-a-text

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 “*”

Formula:

=SUBSTITUTE(SUBSTITUTE(B3, “?”,””),”*”,””)

Remove-multiple-text-by-matching

Replace multiple characters in a text string using VBA

If you want to replace multiple characters in a string, 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:

=MSUB(B3,””,”*”,””,”?”,””,”-“)

MSUB-function-to-replace-multiple-characters-from-a-string

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