How to Remove Text from a Cell in Excel (9 Easy Ways)

There are several methods to remove text from an Excel cell. In this article, I’ll show 9 methods including VBA to remove text from Excel cells. Just have a look at the following methods properly and you’ll find them useful to apply.

Download Practice Book

You can download the free practice Excel template from here and practice on your own.

9 Easy Methods to Remove Text from a Cell in Excel

In the following dataset, you can see the Product Name and Product ID columns. Using this dataset, we will go through 9 easy methods to remove text from the Excel cell. Here, we used Excel 365. You can use any available Excel version.

1. Use of Find and Replace Tool to Remove Text from a Cell in Excel

In this method, we will use the Find and Replace tool to remove “ID” before digits from the Product ID column.

Steps:

• In the beginning, click CTRL+H to open the Find and Replace dialog box.
• After that, write ID in the Find what.
• Then, press Replace All.

• Therefore, you can look that our operation is done and a pop-up notification box is showing the result.
• Then, click OK.

Hence, you can see the ID has been removed from the cells.

Read More: How to Remove Text from an Excel Cell but Leave Numbers (8 Ways)

2. Use of SUBSTITUTE Function to Remove Text from a Cell

In this method, we will use the SUBSTITUTE function to remove text from the Excel cell. The SUBSTITUTE function replaces one or more instances of a given text string.

Steps:

• First of all, type the formula in cell D5 which is given below:

• Then, press ENTER.

Therefore, you can see the result in cell D5.

• After that, copy the formula using Fill Handle for the rest of the cells.

Therefore, you can see the complete Result column.

Read More: How to Remove Letters from Cell in Excel (10 Methods)

3. Applying REPLACE Function to Remove Text from a Cell in Excel

In this method, we will use the REPLACE function to remove text from the Excel cell. The REPLACE function replaces text in a specified position of a supplied string.

Steps:

• First of all, write the formula in cell D5 as given below:

• After that, press ENTER.
• Hence, you can see the result in cell D5.
• Moreover, we will drag down the formula with the Fill Handle tool.

Therefore, you can see the complete Result column.

Read More: How to Remove Specific Text from Cell in Excel (Easiest 11 Ways)

4. Combining MID and LEN Functions

Here in this method, we’ll use the combination of MID and LEN functions to remove certain characters from a cell. The MID function is used to find out strings and return them from any mid part of Excel. And the LEN function is a text function in excel that returns the length of a string/ text.

Steps:

• In the first place, enable editing in cell D5.
• Then, type the formula.

• Then, to see the output just press ENTER.
• Therefore, you can see the result in cell D5.
• Moreover, we will drag down the formula with the Fill Handle tool.

• As a result, you can see the complete Result.

5. Merging RIGHT and LEN Functions

Now I’ll use the RIGHT and LEN functions together to remove text from the Excel cell. The RIGHT function gives the number of characters from the right side of the string.

Steps:

• First of all, in cell D5 type the given formula:

• At the moment, press ENTER.
• Therefore, you can see the result in cell D5.
• Moreover, we will drag down the formula with the Fill Handle tool.

Hence, you can see the complete Result column.

Read More: How to Remove Text before a Space with Excel Formula (5 Methods)

6. Use of LEFT and FIND Functions to Remove Text from a Cell in Excel

Here we’ll use the LEFT and FIND functions together to remove text from the Excel cell. The LEFT function is used to extract text from the left side of a text string. And the FIND function returns the location of a substring in a string. I have rearranged my dataset as the image below. Now I’ll remove the characters after ‘#’ including itself from every cell.

Steps:

• First of all, we will activate cell D5 and write the formula given below:

• After that, press the ENTER button to get the result.
• Therefore, you can see the result in cell D5.
• Then, to apply it to the other cells just use the Fill Handle.

Hence, you can see the complete Result column.

Read More: How to Remove Text After Character in Excel (3 Ways)

7. Removing Nth Instance of Certain Character

Now we’ll use the SUBSTITUTE function to remove the nth instance of a certain character. That’s why I have rearranged my dataset like the image below. See that there are two ‘#’  in every cell. I’ll remove the first ‘#’.

Steps:

• First of all, we will activate cell D5 and type the formula given below:

• Then hit ENTER.
• Hence, you can see the result in cell D5.

• After that, to copy the formula for the other cells you can use the AutoFill feature.

As a result, you can see the complete Result column.

8. Applying Flash Fill Feature

In this method, we will use the Flash Fill feature to remove text from Excel cell. This is the easiest of all methods.

Steps:

• First, type the digits you want to keep in cell D5.
• Then, when you are going to type in the next cell then Excel will catch the pattern and will show it.

• After that, just press the ENTER button and all cells will be filled with that pattern.

Read More: How to Remove Text between Two Characters in Excel (3 Easy Ways)

9. Use of VBA Code to Remove Text from a Cell

Lastly, we will describe the Excel VBA method to remove text from an Excel cell.

Steps

• First of all, we will select the cell ranges where we want to apply VBA.
• Here, we select cells D5:D9.
• After that, we Right-click on the mouse to the title name of the sheet.
• Then, we select View Code from the context menu.

• At this moment, a VBA window will open up.
• Then, we will type the codes given below:

Sub Delete_Last_Char()
n = Int(InputBox("Type Number of Last Characters tobe Removed: "))
For i = 1 To Selection.Rows.Count
   Selection.Cells(i, 1) = Left(Selection. Cells(i, 1), _
   Len(Selection. Cells(i, 1)) - n)
Next i
End Sub

• Afterward, click the Run button to run the code.
• Next, an Input window will appear.
• Then, type the number of characters that you want to remove.
• Here, we typed 3.
• In addition, press OK.

Therefore, the last 3 characters in the Result column are removed.

Read More: How to Remove Specific Text from a Column in Excel (8 Ways)

Practice Section

You can download the above Excel file and practice the explained methods.

Conclusion

Here, we show you 9 methods to remove text from the Excel cell. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section. You can visit our website Exceldemy for more related articles.

Related Article

• How to Delete Defined Names in Excel (3 Ways)