How to Remove More or Less than 3 Words in a Cell

[yogamast12]

I have huge list of keywords contains 1 word to 5 words in a cell..

The keywords that having only 3 words should remain in the cell and others has to be removed

Ex. This is the actual keywords I have
doctor ps2
doctor salary
doctor clipart
doctor government agencies
doctor government grants
doctor homeland security
doctor thesaurus
doctor who
doctor wii
doctor post office jobs
doctor survival kit

It should like this after trim
doctor government agencies
doctor government grants
doctor homeland security
doctor survival kit

You can see the above having only 3 words in a cell remains and others are removed, how can I do this in Excel?

Thanks for the support.

[Leith Ross]

Hello yogamast12,

Welcome to the Forum!

This macro will leave only the rows in column "A" that have 3 words in them.

Sub RemoveKeywords()

  Dim Cell As Range
  Dim DelCells As Range
  Dim KeyWords As Variant
  Dim LastRow As Long
  Dim Rng As Range
  Dim Wks As Worksheet
  
    Set Wks = ActiveSheet
    
    Set Rng = Wks.Range("A1")
    
      LastRow = Wks.Cells(Rows.Count, Rng.Column).End(xlUp).Row
      If LastRow < Rng.Row Then Exit Sub
      
      Set Rng = Rng.Resize(RowSize:=LastRow - Rng.Row + 1)
      
        For Each Cell In Rng
          KeyWords = Split(Trim(Cell), " ")
          If UBound(KeyWords) <> 2 Then
             If DelCells Is Nothing Then Set DelCells = Cell
             Set DelCells = Union(DelCells, Cell)
          End If
        Next Cell
             
      If Not DelCells Is Nothing Then DelCells.Delete
      
End Sub

Adding the Macro

1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
   
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

[yogamast12]

Thanks and that's awesome... Works exactly what I want..

[rtworx]

This worked perfectly as advised. Thanks!