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,
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.
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.
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:
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.
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.
Now, column C will show the phone numbers without the hyphens and parenthesis.