< Go Back

Remove text by matching

You can remove text in Excel by matching the content. It is a very useful feature when working with text. The SUBSTITUTE function lets you remove text by matching. In this tutorial, you will learn how to remove text with the SUBSTITUTE function by matching in Excel.

How to Remove Text by Matching

Using the SUBSTITUTE function with the character you want to remove provided as an argument, you can remove it. The formula to do this follows the syntax,=SUBSTITUTE(text, text_to_remove,"").

This formula follows a very straightforward approach. You provide the string you want to remove the text from as the text argument. The next argument is the text you want to remove by matching the content. SUBSTITUTE then removes all instances of the text specified and returns the result.

Example

The following example contains an employee information directory. Column A has the employee names and column B has the phone numbers. To remove the hyphens (-) from the phone numbers in column B and show the results in column C:

  • Go to cell C2 and select it with your mouse.
  • Assign the formula,=SUBSTITUTE(B2,"-","") to cell C2.
  • Press Enter to apply the formula to cell C2.
  • Drag the fill handle from cell C2 to C7 to copy the formula in column C.

                                  Figure 1. Example of Removing Text by Matching

This will remove all the hyphens from the numbers in column B.

How to Remove More than one Character

You can also nest the SUBSTITUTE function to remove more than one thing. The generic formula to do this is:

=SUBSTITUTE(SUBSTITUTE(text,text_to_remove_1,””),text_to_remove2,””)

Here, you nest a second SUBSTITUTE function inside the SUBSTITUTE function. The operation starts from the one that is nested. Excel removes text_to_remove_1. Then it comes to the outer SUBSTITUTE function and removes text_to_remove_2.

Example

To remove the opening ( “(“ ) and the closing parenthesis ( “)“ ) along with the hyphens (“-“) from the phone numbers of the previous example.

  • Select cell C2.
  • Write the formula,=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"-",""),"(",""),")","") in the formula bar.
  • Click Enter.
  • Drag the formula from cell C2 to C7 to copy the formula in column C.

                               Figure 2. Example of Removing Multiple Things by Matching

Now, column C will show the phone numbers without the hyphens and parenthesis.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar