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.

excel delete columns with specific text

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.

Using Find & Select Feature

Using Find & Select Feature

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

Using Find & Select Feature

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

Using Find & Select Feature

  • Now select Home > Delete > Delete Sheet Columns.

Using Find & Select Feature

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

Using Find & Select Feature

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.

Utilizing Keyboard Shortcut

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

Utilizing Keyboard Shortcut

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

Utilizing Keyboard Shortcut

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


Similar Readings


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.

Incorporating VBA Code

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

Incorporating VBA Code

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

Incorporating VBA Code

  • Click on the Developer > Macros.

Incorporating VBA Code

  • This will open the Macros dialog box. Then select your function, which in this case is DeleteColumnswithSpecificValue then press the Run button.

Incorporating VBA Code

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

Incorporating VBA Code

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.

How to Delete Multiple Columns That Are Blank

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

How to Delete Multiple Columns That Are Blank

  • Select Blanks and press OK.

How to Delete Multiple Columns That Are Blank

  • Thus, the blank space will be selected.

How to Delete Multiple Columns That Are Blank

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

How to Delete Multiple Columns That Are Blank

  • This will bring a result as given below.

How to Delete Multiple Columns That Are Blank

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.

How to Delete Rows Without Specific Text in Excel

  • 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

How to Delete Rows Without Specific Text in Excel

  • Now select your Range in which you wish to do your task.

How to Delete Rows Without Specific Text in Excel

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

How to Delete Rows Without Specific Text in Excel

  • Thus, the output is as follows.

How to Delete Rows Without Specific Text in Excel

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.

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

Mohammad Shah Miran

Mohammad Shah Miran

Miran is a highly motivated individual with a strong educational background in engineering. He is interested in technology and passionate about creating engaging and informative content. After graduation, Miran decided to pursue a career in content development and has been working in the field for some time. Miran is dedicated to staying current with the latest trends and technologies and is eager to continue learning and growing as a professional.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo