Excel Guides

Making PROPER Skip Certain Words in Excel

If you want to make skip certain words in Excel, there are a few things you need to do. First, you need to open up the Excel application. Next, click on the 'File' tab and then select 'Open.' Once the Open dialog box appears, locate and select the file you want to edit. After the file is open, click on the 'Insert' tab and then click on the 'Text Box' button. A text box will appear in your worksheet. Type the text you want to appear in your text box and then format it using the various options in the 'Format' tab. When you are finished, click on the 'Close' button.

Now that you have created your text box, it's time to add some code that will make Excel skip certain words. To do this, click on the 'Developer' tab and then click on the 'Visual Basic' button. This will open up the Visual Basic Editor. In the editor, click on 'Insert' and then 'Module.' A module will appear in your workbook. Paste the following code into your module:

Sub SkipWords()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim m As Integer
Dim n As Integer
 Dim LastRow As Long

  LastRow = Range("A" & Rows.Count).End(xlUp).Row

 For i = 1 To LastRow

    If Cells(i, 1) = "Skip" Then

       j = i + 1

       Do While Not IsEmpty(Cells(j, 1))

          k = j + 1

          Do While Not IsEmpty(Cells(k, 1))

             l = k + 1

             Do While Not IsEmpty(Cells(l, 1))

                m = l + 1

                Do While Not IsEmpty(Cells(m, 1))

                   n = m + 1

                   If Cells(n, 1) <> "Skip" Then Exit Do

                   m = m + 1

                Loop

                If Cells(n, 1) <> "Skip" Then Exit Do

                l = l + 1

             Loop

             If Cells(n, 1) <> "Skip" Then Exit Do

             k = k + 1

          Loop

          If Cells(n, 1) <> "Skip" Then Exit Do

          j = j + 1

       Loop        
  End If     
Next i  
End Sub

This code will look for the word 'Skip' in column A of your spreadsheet and then skip over the next five words. So if you have a list of words like this:

  1. One
  2. Two
  3. Skip
  4. Three
  5. Four
  6. Skip

(Notice that there are two instances of the word 'Skip.')

The code will skip over 'Three' and 'Four' and go to the next word after 'Four,' which is 'Five.' The output would look like this:

  1. One
  2. Two
  3. Skip
  4. Skip
  5. Five

(Notice that 'Three' and 'Four' are missing from the output.)

If you want to skip more than five words at a time, simply change the number 5 in the code to whatever number you want.

(e.g., Change 5 to 10 if you want to skip over 10 words at a time.) 

That's all there is to it! I hope this tutorial was helpful.

.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.