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

This article illustrates 8 different methods for how remove specific text from a column in excel. Deleting specific redundant text manually from a column in your dataset will be a tedious task. This article will help you to do that with simple formulas and VBA in excel. The following picture highlights the purpose of the article. Have a quick look through it to see how to do that.

Download Practice Workbook

You can download the practice workbook from the download button below.

8 Ways to Remove Specific Text from a Column in Excel

Here I am going to illustrate the methods. So let’s begin.

1. Remove Specific Text from a Column with Find & Replace Feature

You can use the Find and Replace feature to delete particular text from a column in excel.

📌 Steps

• First, select your data in the required column.
• Then press CTRL+H to open the Find and Replace window.
• Next, enter the specific text (.com) you want to remove in the Find what box.
• Then clear the Replace with box or keep it empty.
• After that, select the Replace All button.

Finally, you will see the specific text removed from the entire column as shown below.👇

1.1 Remove Text before a Specific Text or Character in Excel

You can also remove all the texts before a specific text or a particular character (@) with the Find and Replace feature in excel.

📌 Steps

• First, select the target dataset (B5:B9).
• Then press CTRL+H.
• Next, enter an asterisk (*) followed by the particular text or character (@) in the Find what box.
• After that, clear the Replace with box or keep it empty.
• Then, select the Replace All button.

Finally, you will see all the text before the particular character is removed as shown below.👇

1.2 Remove Text after a Specific Text or Character in Excel

You can remove texts after a specific text or a particular character with this feature as well.

📌 Steps

• At first, select the entire dataset.
• Next, press CTRL+H.
• Then, enter the particular text or character (@) and an asterisk (*) after that in the Find what box as shown below.
• After that, clear the Replace with box or keep it empty.
• Then, select the Replace All button.

Now you will see all the text after the particular character is removed along with it.

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

2. Remove Specific Text from a Column with SUBSTITUTE Function

You can use the SUBSTITUTE function to delete specific text in excel. It uses the following arguments.

📌 Steps

• First, enter the following formula in cell C5.

• Then drag the fill handle icon or double-click on it.

After that, you will see the specific text (.com) is removed as shown in the picture below.👇

2.1 Remove Multiple Specific Text from a Column in Excel

You can also remove/replace multiple particular texts from a column with this function. We will use multiple characters to demonstrate the method. You can use the desired texts instead.

📌 Steps

• First, enter the following formula in cell C5.

• Then drag the fill handle icon or double-click on it.

After that, all the specific characters are replaced with new characters. You can remove the characters as well by only using the double quotes (“”) instead of new characters in between them.

🔎 How Does the Formula Work?

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

3. Delete Specific Text from a Column with LEFT and FIND Functions

Alternatively, you can use the LEFT function to remove specific text in excel. It uses the following arguments and returns the specified number of characters from the left of a text string.

We will use the FIND function for the [num_chars] argument. It has the following arguments and returns the starting position of a text string within another text.

📌 Steps

• At first, enter the following formula in cell C5.

• Then double click on the fill handle icon or drag it to the cells below.

After that, you will get the same results as obtained in the earlier methods.

Read More: How to Remove Text From Excel Cell (9 Easy Ways)

4. Remove Specific Text from a Column with MID and FIND Functions

You can also do that with the MID function. It uses the following arguments and returns the specified number of characters from the middle of a text string.

We will use the FIND function for the [num_chars] argument here too.

📌 Steps

• At first, enter the following formula in cell C5.

• Then double click on the fill handle icon or drag it to the cells below.

After that, you will get the same results as obtained in the earlier methods.

5. Remove Specific Text from a Column Combining RIGHT, LEN, and FIND Function

Another alternate way to remove specific text is by using the RIGHT, LEN, and FIND functions together.

The RIGHT function has the following arguments and returns the specified number of characters from the right of a text string.

The LEN function returns the number of total characters in a text string.

📌 Steps

• At first, enter the following formula in cell C5.

• Then double click on the fill handle icon or drag it to the cells below.

Finally, you will see the following results.👇

🔎 How Does the Formula Work?

6. Delete Specific Text from a Column with REPLACE Function

The Replace function in excel also allows us to remove specific text from a text string. It has the following arguments.

📌 Steps

• At first, enter the following formula in cell C5.

• Then double click on the fill handle icon or drag it to the cells below.

Finally, you will see the following results.👇

7. Remove Specific Text from a Column with Flash Fill

Using the Flash Fill feature can be another alternative way to remove a specific text from a column in excel. Follow the steps below to do that.

📌 Steps

• At first, assume you want to remove the titles () from a list of names (B5:B9).
• Then enter the first name in cell C5 excluding the title (Bill Ayers).
• Now as soon as you start typing the next name in cell C6, you will see a greyed-out flash fill list of all the names in column B as shown below.
• If you don’t see the flash fill list, then enter the name in cell C6 and start typing in cell C7.
• After that, you should see the grayed-out list.

Finally, hit the Enter key to get the desired result.

Alternatively, you can use the CTRL+E shortcut for the flash fill after entering the name in cell C5.

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

8. Remove Particular Text from a Column with Excel VBA

You can use Excel VBA to do that too. Follow the steps below.

📌 Steps

• First, press ALT+F11 to open the VBA window.
• Then select Insert >> Module as shown below.
• After that, a new blank module will open.

• Now copy the following code using the copy button.

Sub RemoveSpecificText()
Dim EndRow As Long
Dim Names As Range
EndRow = Cells(Rows.Count, "B").End(xlUp).Row
Set Names = Range("B5:B" & EndRow)
Names.Replace What:="Dr. ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

• Then paste the copied code on the blank module.

• After that, keep the cursor in the code and press F5 to run it.

Finally, you will see the following result.👇

Things to Remember

• Make sure the Replace tab is selected while using the Find and Replace feature.
• You must put the text that you want to remove inside double quotes (“”) in the formulas.

Conclusion

Now you know 8 different ways to remove specific text from a column in excel. Please let us know which method you prefer the most. You can also use the comment section below for further queries or suggestions. Do visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.

Related Articles

• How to Remove Letters from Cell in Excel (10 Methods)
• Remove Text After Character in Excel (3 Ways)
• How to Remove Specific Text from Cell in Excel (Easiest 11 Ways)