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

Get FREE Advanced Excel Exercises with Solutions!

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.

excel delete columns with specific text

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.

Using Find & Select Feature

Using Find & Select Feature

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

Using Find & Select Feature

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

Using Find & Select Feature

  • 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


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.

Utilizing Keyboard Shortcut

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

Utilizing Keyboard Shortcut

  • The output is given below.

Utilizing Keyboard Shortcut


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

  • Go to the Insert tab > Select Module.

Incorporating VBA Code

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

Incorporating VBA Code

  • Click on the Developer > Macros.

Incorporating VBA Code

  • This will open the Macro Name dialog box. 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


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.

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

  • Click Home > Delete > 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: How to Delete Blank Columns in Excel


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.

How to Delete Rows Without Specific Text in Excel

  • 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

How to Delete Rows Without Specific Text in Excel

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

How to Delete Rows Without Specific Text in Excel

  • Thus, the output is as follows.

How to Delete Rows Without Specific Text in Excel


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


Download Practice Workbook


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
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. He is eager to continue learning and growing as a professional.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo