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.

Download Practice Workbook

You can download and practice the dataset that we have used to prepare this article.

3 Method to Delete Columns with Specific Text in Excel

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 Excel 365 version; you may use any other version according to your convenience.

Method 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 which contains “Ohio”. To do so we will use Excel dedicated Find & Select feature.

📌 Steps:

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

• Thus, a dialogue box as below will appear. In Find What box, type the word you are looking for and select the Find All option afterwards. In our case the intended word is ‘’Ohio’’.
• Select Ctrl + A shortcut to select all the value and click close.

• Thus, all the cells containing “Ohio” (here two cells contain the word) will be selected as below.

• Now 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.

Read More: How to Delete Multiple Columns in Excel with Condition

Method 2: Utilizing Keyboard Shortcut

If you have a small or medium size 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.

• Immediately, a small dialogue box will appear as like below.
• Next, pick Entire Column and then press OK.

• The output is given below. Easy, isn’t it?

Read More: VBA to Delete Column in Excel (9 Criteria)

Similar Readings

• [Solved!] Can’t Delete Extra Columns in Excel (3 Solutions)
• How to Delete Columns without Losing Formula in Excel (3 Easy Steps)
• Delete Multiple Columns by Number Using VBA in Excel (4 Ways)
• How to Delete Extra Columns in Excel (7 Methods)
• Delete Unused Columns in Excel(Easiest 5 Methods)

Method 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.

• Second, go to the Insert tab >> select Module.

• Now to make your task easy, Copy and Paste the following VBA Code in your Module1

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

⚡ 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. Which 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 Macros dialog box. Then select your function, which in this case is DeleteColumnswithSpecificValue then press the Run button.

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

Read More: VBA Macro to Delete Columns Based on Criteria in Excel (8 Examples)

How to Delete Multiple Columns That Are Blank in Excel

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

📌 Steps:

• Select your range of data firstly.

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

• Select Blanks and press OK.

• Thus, the blank space will be selected.

• Now click Home > Delete button > Delete Sheet Columns.

• This will bring a result as given below.

Read More: Delete Blank Columns in Excel (3 Ways)

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
• Then press Insert > Module to open a blank module.

• Copy and Paste the following code in your VBA module and then press 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 your 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 afterwards

• Thus, the output is as follows.

Read More: How to Clear Contents in Excel Without Deleting Formulas (3 Ways)

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.

Conclusion

I hope you have learned something new today through this tutorial. This is an easy task and i think all of us should learn this method to make our life little bit easier. Further, If you have any queries, feel free to comment below and I will get back to you soon.

Related Articles

• How to Delete Every Other Column in Excel (5 Easy ways)
• Macro to Delete Columns in Excel (10 Methods)
• How to Delete Hidden Columns in Excel (2 Simple Methods)
• Delete Empty Columns with Header in Excel (4 Easy Ways)
• How to Delete Columns in Excel That Go on Forever (6 Ways)
• Can’t Delete Blank Columns in Excel (3 Problems & Solutions)
• How to Delete Infinite Columns in Excel (4 Methods)