Remove Stop Words or Custom Words from Text Strings in Excel

When working with text data in Excel, you may need to remove common stop words such as a, the, it, of, or delete any custom words from sentences in a column. This is useful when cleaning imported data, preparing keyword lists, simplifying text for analysis, or standardizing content before further processing.

In this guide, you will learn three practical ways to remove stop words or other specific words in Excel, each method is suited to different Excel versions and needs.

Why remove stop words or specific words in Excel?

Remove stop words or other specific words in Excel

• Method1: Remove stop words with formulas (Excel 365)
• Method 2: Remove stop words with VBA code
• Method 3: Remove stop words with Kutools AI Aide

Tips for better results

Conclusion

Why remove stop words or specific words in Excel?

Remove stop words or other specific words in Excel

Common words like "the," "is," "and," and "at" (known as stop words) take up space without adding real meaning. If you’ve ever tried to create a clean word cloud or categorize search terms only to find "the" is your most frequent result, you know exactly how frustrating this can be. Manually deleting these words is a recipe for burnout, but fortunately, Excel has evolved. This section will introduce 3 easy ways to deal with this task.

Method1: Remove stop words with formulas (Excel 365)

If you are using Excel 365, you can remove stop words with formulas. By combining modern functions such as TEXTSPLIT, FILTER, MATCH, and TEXTJOIN, Excel can split each sentence into individual words, compare them with a stop word list, remove the matching words, and then join the remaining words back into a cleaned sentence. This method is ideal if you want a dynamic solution that updates automatically when the original text or stop word list changes.

In this example, the original sentences are stored in Column A, the stop words are listed in Column D, and the cleaned results will be returned in Column B.

1. In B2, enter the following formula:

   =TEXTJOIN(" ",TRUE,FILTER(TEXTSPLIT(TRIM(A2)," "),ISNA(MATCH(LOWER(TEXTSPLIT(TRIM(A2)," ")),LOWER($D$2:$D$8),0))))

   Note: In this formula, A2 represents the source text, and $D$2:$D$8 is the range containing your list of stop words.
2. Press Enter key, Excel will return the sentence without the words listed in D2:D8.
   
3. Then, drag the fill handle down to apply the formula to the rest of the cells.
   

Method 2: Remove Stop Words with VBA Code

For users with older versions of Excel or those who need to clean thousands of rows at once, a User Defined Function (UDF) via VBA is the most efficient route.

1. Hold down the ALT + F11 key to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.

   Function CleanStopWords(Txt As String, StopWordsRange As Range) As String
       Dim words() As String
       Dim word As Variant
       Dim cell As Range
       Dim result As String
       
       words = Split(Txt, " ")
       
       For Each word In words
           Dim isStopWord As Boolean
           isStopWord = False
           For Each cell In StopWordsRange
               If LCase(Trim(word)) = LCase(Trim(cell.Value)) Then
                   isStopWord = True
                   Exit For
               End If
           Next cell
           
           If Not isStopWord Then
               result = result & word & " "
           End If
       Next word
       
       CleanStopWords = Trim(result)
   End Function

3. Then, save this code and go back to the worksheet. Type the following formula:

   =CleanStopWords(A2, $D$2:$D$8)

   Note: In this formula, A2 represents the source text, and $D$2:$D$8 is the range containing your list of stop words.
4. Then, drag the fill handle down to apply the formula to the rest of the cells.
   

Method 3: Remove stop words with Kutools AI Aide

If you prefer a simpler, more intelligent way without writing formulas or VBA, Kutools AI Aide can help. It is especially useful when:

• your sentences contain irregular text
• punctuation is inconsistent
• you want to remove stop words based on a custom instruction
• you want a more user-friendly solution

Kutools AI Aide allows you to process text with natural language prompts instead of building complicated formulas.

1. Click Kutools > Kutools AI > Cells Aide, see screenshot:
   
2. In the Cells AI All-in-One Aide dialog box, specify the following options:
   1. In the Data source range box, select the cells that contain the text strings you want to process;
   2. Click the Prompt Library dropdown and select Remove Stop Words as the predefined prompt.
   3. In the Prompt Content box, review the prompt and modify it as needed, or add your own custom words to remove based on your specific needs.
   4. Click the Generate button. Kutools AI will analyze the selected data and display the result in the Result panel on the right.
      
3. After the AI returns the results, click Insert to Range or choose a blank area to place the output. Finally, click OK.

Tips for better results

Conclusion

There are several effective ways to remove stop words or other specific words in Excel, and the best choice depends on your Excel version and workflow.

If you use Excel 365, the formula method is a quick and efficient solution. If you need more control or use an older version of Excel, VBA is a strong option for batch processing. And if you prefer the easiest and most flexible approach, Kutools AI Aide can help you clean text with simple natural language prompts.

Choose the method that best fits your needs, and you can quickly turn messy sentence data into cleaner, more useful text.