How to Delete Columns with Specific Text in Excel (3 Methods)

You may frequently need to delete a full column or multiple columns in Excel, depending on the situation. For instance, you are asked to delete an entire column whenever you find a specific text. How will you do that? Well, this article will illustrate 3 different ways to delete columns with specific text in Excel.

How to Delete Columns with Specific Text in Excel: 3 Methods

Let’s assume we have a dataset, namely Monthly Itinerary for a Group of People. You can use any dataset suitable for you.

Here, we have used the Microsoft Office 365 version; you may use any other version according to your convenience.

1. Using Find & Select Feature

Let’s say the dataset which has been given above contains several city names. We have to delete the column that contains Ohio. To do so we will use Excel’s dedicated Find & Select feature.

📌 Steps:

First, go to Home > Find & Select > Find.

A dialogue box as below will appear. In the Find What box, type the word you are looking for and select the Find All option afterward. In our case, the word is Ohio.
Select Ctrl + A shortcut to select all the values and click close.

All the cells containing Ohio (here two cells contain the word) will be selected as below.

Select Home > Delete > Delete Sheet Columns.

As a result, all the columns containing specific text (e.g. Ohio) are deleted. The final output has been given below.

2. Using Keyboard Shortcut

If you have a small or medium-sized dataset and wish to learn a shortcut but effective method, then this method is for you. In this method, we will use a keyboard shortcut.

📌 Steps:

Select Ohio on your dataset by pressing Ctrl simultaneously.
Right-click on your mouse.
Then, pick the Delete option from the Context Menu.

A small dialogue box will appear as like below.
Next, pick the Entire column and then press OK.

The output is given below.

3. Incorporating VBA Code

We can do the same task by applying VBA code to your Excel file, which requires less effort in my opinion. More importantly, it will be applicable to a larger dataset.

📌 Steps:

First of all, proceed to the Developer tab > Click on Visual Basic.

Go to the Insert tab > Select Module.

Use the Copy and Paste feature to copy the following VBA code in your module 1.

Sub DeleteColumnswithSpecificValue()
Dim cell As Range
For Each cell In Range("B2:F9")
If cell.Value = "Ohio" Then
cell.EntireColumn.Delete

End If

Next cell
End Sub

Close the VBA window.

⚡ Code Breakdown:

Now, we will explain how the given VBA code works. The code is divided into 2 steps.

In the first portion, you have to select the range in which your intended words reside.
Next, call your intended word as a string. This means you will put your word into a pair of inverted commas as we have shown below.

Click on the Developer > Macros.

This will open the Macro Name dialog box. Select your function, which in this case is DeleteColumnswithSpecificValue, then press the Run button.

That takes it all. The final output is given below.

How to Delete Multiple Columns That Are Blank in Excel

If you are thinking of deleting multiple columns or those columns that contain blank cells, the demonstration given below may help you.

📌 Steps:

Select your range of data first.

Go to Home > Find & Select > Go To Special.

Select Blanks and press OK.

Thus, the blank space will be selected.

Click Home > Delete > Delete Sheet Columns.

This will bring a result as given below.

How to Delete Rows Without Specific Text in Excel

Now think about the opposite scenario, where you have been told to remove the rows without specific text. How will you do that? Let’s see…

📌 Steps:

Press Alt + F11 to open your Microsoft Visual Basic window.
Then press Insert > Module to open a blank module.

Copy and paste the following code into your VBA module and then press the F5 key to run your code.

Sub DelRowsNotContainSpecificTexr()
    Set findRange = Application.Selection
    Set findRange = Application.InputBox("Select one Range which contain texts that you want to delete rows based on", "DelRowsNotContainCertainText", findRange.Address, Type:=8)
    findTexr = Application.InputBox("Please type a certain text", "DelRowsNotContainSpecificTexr", "", Type:=2)
    For i = findRange.Rows.Count To 1 Step -1
        Set findRow = findRange.Rows(i)
        Set findCell = findRow.Find(findTexr, LookIn:=xlValues)
        If findCell Is Nothing Then
        findRow.Delete
        End If
    Next
End Sub

Now select the range in which you wish to do your task.

Then put the name of your word in the appeared box to keep the row protected in which it belongs.
Press OK.

Thus, the output is as follows.

Practice Section

We have provided a practice section on the right side of each sheet so you can practice yourself. Please make sure to do it yourself.

Download Practice Workbook

Delete Columns with Specific Text.xlsm

Conclusion

I hope you have learned a new thing today through this tutorial which is how to delete columns with specific text in Excel. This is an easy task and I think all of us should learn this method to make our life a little bit easier. Further, If you have any queries, feel free to comment below and I will get back to you soon.

<< Go Back to Delete Columns | Columns in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Delete Columns with Specific Text in Excel (3 Methods)

How to Delete Columns with Specific Text in Excel: 3 Methods

1. Using Find & Select Feature

2. Using Keyboard Shortcut

3. Incorporating VBA Code

How to Delete Multiple Columns That Are Blank in Excel

How to Delete Rows Without Specific Text in Excel

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply