Google Sheets: Ignore Strings In Unique Word List

Aug 12, 2025 by Felix Dubois 50 views

How to Ignore Specific Strings When Creating a Unique Word List in Google Sheets

Hey guys! Ever run into a situation in Google Sheets where you're trying to create a unique list of words, but you've got some pesky strings you just want to ignore? It's a common problem, and thankfully, there are some neat tricks we can use to get around it. In this article, we'll dive deep into how you can filter out those unwanted strings and get the clean, unique word list you're after. So, let's get started!

Understanding the Basics: Creating a Unique List of Words

Before we jump into ignoring specific strings, let's quickly recap how to create a unique list of words in Google Sheets. The formula that's often used looks something like this:

=UNIQUE(TRANSPOSE(SPLIT(ArrayFormula(CONCATENATE(B2:E&"+")), "+")))

Let's break this down to understand what's happening:

CONCATENATE(B2:E&"+"): This part takes the range of cells (B2 to E in this example) and joins all the text together into one big string. The &"+" adds a "+" sign after each cell's content, which acts as a delimiter.
ArrayFormula(...): This ensures that the CONCATENATE function works across the entire range, even if it's multiple rows and columns.
SPLIT(..., "+"): This splits the big string into individual words, using the "+" delimiter we added earlier.
TRANSPOSE(...): This flips the list of words from horizontal to vertical, making it easier to read.
UNIQUE(...): This is the magic! It filters out any duplicate words, leaving you with a list of unique terms.

So, this formula is a great starting point, but what if you want to ignore specific strings? That's where things get a little more interesting.

The Challenge: Filtering Out Unwanted Strings

Imagine you're analyzing customer feedback, and you've got a sheet full of comments. You want to create a list of unique words to identify common themes, but you want to exclude common words like "the," "a," "is," and so on. These words, known as stop words, don't really add much value to your analysis. Or, perhaps you have some specific product names or irrelevant terms you want to exclude.

The standard UNIQUE formula doesn't have a built-in way to filter out specific strings. That's where we need to get a bit creative and use some other functions to help us out. We'll explore a few different methods to achieve this, giving you the flexibility to choose the one that best suits your needs.

Method 1: Using the `REGEXREPLACE` Function

One powerful way to ignore certain strings is to use the REGEXREPLACE function. This function allows you to replace text that matches a regular expression. We can use this to replace the strings we want to ignore with an empty string, effectively removing them from the list before we create the unique list.

Here's how it works:

Identify the strings to ignore: First, you need to decide which strings you want to exclude. Let's say we want to ignore the words "the," "a," and "is."
Create a regular expression: We'll create a regular expression that matches these words. The expression will look something like this: "\b(the|a|is)\b". Let's break this down:
- \b: This matches a word boundary, ensuring that we only match whole words (e.g., "the" but not "there").
- (the|a|is): This is a group that matches either "the," "a," or "is." The | acts as an "or" operator.
Use REGEXREPLACE: We'll use the REGEXREPLACE function to replace the matched strings with an empty string. The formula will look something like this:
```
=REGEXREPLACE(A1, "\b(the|a|is)\b", "")
```
Where A1 is the cell containing the text you want to clean.
Integrate into the UNIQUE formula: Now, we need to integrate this into our original UNIQUE formula. We'll apply the REGEXREPLACE function to the range of cells before we concatenate them. The updated formula will look like this:
```
=UNIQUE(TRANSPOSE(SPLIT(ArrayFormula(CONCATENATE(REGEXREPLACE(B2:E, "\b(the|a|is)\b", "")&"+")), "+")))
```
This formula first replaces the unwanted strings with empty strings, then concatenates the cleaned text, splits it into words, transposes the list, and finally, creates a unique list.

Example:

Let's say your data in cells B2:E looks like this:

B2	C2	D2	E2
The quick brown fox	a lazy dog	is jumping over the	lazy river.

After applying the formula, the unique list will be:

quick
brown
fox
lazy
dog
jumping
over
river.

Notice how "the," "a," and "is" are gone!

Method 2: Using the `FILTER` Function with `REGEXMATCH`

Another approach is to use the FILTER function in combination with REGEXMATCH. This method allows you to filter out words that match a specific pattern. It's a bit more involved than the REGEXREPLACE method, but it offers more flexibility.

Here's the breakdown:

Create the initial list of words: Start with the basic formula to create a list of words (without the UNIQUE function yet):
```
=TRANSPOSE(SPLIT(ArrayFormula(CONCATENATE(B2:E&"+")), "+"))
```
Create a list of strings to ignore: Create a separate range of cells containing the strings you want to ignore. Let's say you put these strings in cells G1:G3 (e.g., G1 = "the", G2 = "a", G3 = "is").
Use REGEXMATCH to identify unwanted words: We'll use REGEXMATCH to check if each word in our list matches any of the strings we want to ignore. The formula will look something like this:
```
=ArrayFormula(REGEXMATCH(word_list, CONCATENATE(G1:G3)))
```
Where word_list is the range containing the list of words from step 1. This formula will return an array of TRUE/FALSE values, indicating whether each word matches any of the strings in G1:G3.
Use FILTER to exclude unwanted words: Now, we'll use the FILTER function to exclude the words that match the strings we want to ignore. The formula will look like this:
```
=FILTER(word_list, NOT(ArrayFormula(REGEXMATCH(word_list, CONCATENATE(G1:G3)))))
```
This formula filters the word_list, keeping only the words where the REGEXMATCH result is FALSE (i.e., the words that don't match any of the strings in G1:G3).
Apply the UNIQUE function: Finally, we'll apply the UNIQUE function to get the unique list of words:
```
=UNIQUE(FILTER(TRANSPOSE(SPLIT(ArrayFormula(CONCATENATE(B2:E&"+")), "+")), NOT(ArrayFormula(REGEXMATCH(TRANSPOSE(SPLIT(ArrayFormula(CONCATENATE(B2:E&"+")), "+")), CONCATENATE(G1:G3))))))
```
This formula combines all the steps to create a unique list of words while ignoring specific strings.

Example:

Using the same data as before (B2:E), and with G1:G3 containing "the," "a," and "is," the result will be the same unique list:

quick
brown
fox
lazy
dog
jumping
over
river.

Method 3: Using a Custom Function (Google Apps Script)

For more complex scenarios, you might want to consider using a custom function written in Google Apps Script. This gives you the most flexibility and control over the filtering process.

Here's a basic outline of how you can do it:

Open the Script editor: In your Google Sheet, go to "Tools" > "Script editor."

Write the custom function: Write a function that takes the text range as input, splits it into words, and filters out the unwanted strings. Here's an example:

/**  
 * Creates a unique list of words, excluding specified words.  
 *  
 * @param {range} textRange The range of cells containing the text.  
 * @param {string} wordsToIgnore A comma-separated list of words to ignore.  
 * @return {Array} A unique list of words.  
 * @customfunction  
 */  
function UNIQUEWORDS(textRange, wordsToIgnore) {  
  // Get the text from the range  
  var text = "";  
  for (var i = 0; i < textRange.length; i++) {  
    for (var j = 0; j < textRange[i].length; j++) {  
      text += textRange[i][j] + " ";  
    }  
  }  

  // Split the text into words  
  var words = text.toLowerCase().split(/\s+/);  

  // Convert wordsToIgnore to lowercase array  
  var ignoreList = wordsToIgnore.toLowerCase().split(",").map(function(word) {  
    return word.trim();  
  });  

  // Filter out the words to ignore  
  var filteredWords = words.filter(function(word) {  
    return ignoreList.indexOf(word) === -1 && word !== "";  
  });  

  // Get unique words  
  var uniqueWords = [];  
  for (var i = 0; i < filteredWords.length; i++) {  
    if (uniqueWords.indexOf(filteredWords[i]) === -1) {  
      uniqueWords.push(filteredWords[i]);  
    }  
  }  

  return uniqueWords.sort();  
}

Save the script: Save the script with a name (e.g., "UniqueWords").
Use the custom function in your sheet: Now, you can use the custom function in your sheet like this:
```
=UNIQUEWORDS(B2:E, "the, a, is")
```
This formula calls the UNIQUEWORDS function, passing the range B2:E and the comma-separated list of words to ignore as arguments. The function will return a unique, sorted list of words, excluding the specified strings.

Explanation:

The script first gets the text from the specified range.
It splits the text into words, converts them to lowercase, and trims whitespace.
It converts the wordsToIgnore string into an array of lowercase words.
It filters out the words that are in the ignoreList.
It creates a unique list of the remaining words.
Finally, it sorts the list and returns it.

Choosing the Right Method

So, which method should you use? It depends on your specific needs:

REGEXREPLACE: This is a good option for simple cases where you have a few strings to ignore and the regular expression is relatively straightforward.
FILTER with REGEXMATCH: This method offers more flexibility for complex filtering scenarios, especially if you have a dynamic list of strings to ignore.
Custom Function: This is the most powerful option for complex scenarios or when you need more control over the filtering process. It's also a good choice if you need to reuse the same filtering logic in multiple sheets.

Best Practices and Tips

Use word boundaries in regular expressions: When using REGEXREPLACE or REGEXMATCH, make sure to use word boundaries (\b) in your regular expressions to avoid accidentally matching parts of words (e.g., matching "the" in "there").
Convert to lowercase: To ensure that you're matching strings regardless of case, convert both the text and the strings to ignore to lowercase using the LOWER function or the .toLowerCase() method in Apps Script.
Test your formulas: Before applying your formula to a large dataset, test it on a small sample to make sure it's working as expected.
Consider performance: For very large datasets, custom functions might be more efficient than complex formulas.

Conclusion

Creating a unique list of words in Google Sheets is a valuable technique for various tasks, from analyzing text data to identifying common themes. By using the methods we've discussed, you can easily ignore certain strings and get the clean, relevant word list you need. Whether you choose REGEXREPLACE, FILTER with REGEXMATCH, or a custom function, you'll be well-equipped to tackle any filtering challenge. So go ahead, give these techniques a try, and happy sheeting, guys!