How to remove text from cell in Excel

It is commonly thought that Microsoft Excel is a tool for handling data and performing data analysis containing numbers only. However, this is just one side of Excel’s data analysis. Excel also provides built-in functions and other tricks to manipulate text data as well.

In this tutorial we’ll learn how to remove some specific text from a cell using Excel. Consider this data set in the picture above having email addresses of different people extracted from a website. All data contains the word mailto: before each email address and we wish to remove it. We can achieve this by using two different methods;

• Using Excel’s Find and Replace
• Using Substitute Function

So let’s learn how we can achieve this by following these steps.

Method 1 – Use Excel’s Find and Replace Tool

In this method we’ll use Excel’s built-in tool Find and Replace to remove the word mailto: from our data set. When we use this method the original data set is changed and the desired word is removed from it. Let’s dive into it and see how it is done.

Step 1 – Select the Data Set

• Select the data from which you wish to remove the desired word.

Step 2 – Use CTRL+H to open Find and Replace Dialog

• Now press CTRL+H and go to the Replace tab.
• In the Find what: type mailto: and leave the Replace with: blank or empty.

Step 3 – Remove the Desired Word from Data

• Now press the Replace all button on this dialog box and you’re done.
• Excel will automatically search for every instance of mailto: in the selected data range and replace it with a blank.

Method 2 – Use Substitute Function

Excel provides us with another simple method to remove text with desired text in a cell by using SUBSTITUTE function. Let’s first understand what inputs this function require and what is the syntax of this function in particular;

= SUBSTITUTE(text, old_text, new_text, [instance])

text: 

This is the original text from which we want to remove any text or character as well. In our example it will be the mailto:johndoe@gmail.com

old_text:

This can be any text, special character or any number, that we wish to remove. In our case, it will be a word i.e. mailto:

new_text:

This is the text that we wish to place when we remove the old text. It can be another text word or nothing i.e. blank as well. To put a blank we’ll use “” character.

[instance]:

This parameter is optional that is why it is enclosed in [ ]. If our text has multiple instances of the text or a special character which we wish to remove then we can input a number in the formula to remove a specific instance only. By default all instances will be removed and we’ll use this default option.

Step 1 – Create the formula

• First select a suitable cell and write the following formula into it;

  =SUBSTITUTE(A2, “mailto:”, “”)

Step 2 – Implement the Formula

• Now implement the formula by pressing the Enter.
• You can extend the formula to all other cells having the same type of text by dragging down the formula through fill handle as shown below;