Counting Words in a Single Cell

To count the number of words in a single cell, you can use a combination of the LEN, TRIM, and SUBSTITUTE functions. Here’s the formula:

=IF(A1=””,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,” “,””))+1)

Let’s break this down:

1. TRIM(A1): Removes any leading or trailing spaces from the text in cell A1.
2. LEN(TRIM(A1)): Calculates the length of the trimmed text.
3. SUBSTITUTE(A1,” “,””): Removes all spaces from the original text in A1.
4. LEN(SUBSTITUTE(A1,” “,””)): Calculates the length of the text without spaces.
5. LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,” “,””)): Gives the number of spaces in the original text.
6. +1: Adds one to the number of spaces to get the word count, since the number of words is always one more than the number of spaces.

Example

Consider cell A1 contains the text: “Google Sheets word count formula”.

• TRIM(A1) results in “Google Sheets word count formula”.
• LEN(TRIM(A1)) gives 30.
• SUBSTITUTE(A1,” “,””) results in “GoogleSheetswordcountformula”.
• LEN(SUBSTITUTE(A1,” “,””)) gives 26.
• LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,” “,””)) gives 4.
• Adding 1 results in 5, the number of words.

Counting Words in a Range of Cells

To count words across a range of cells, you can use an array formula. Here’s how:

=SUM(ARRAYFORMULA(LEN(TRIM(A1:A10))-LEN(SUBSTITUTE(A1:A10,” “,””))+1))

This formula works similarly to the single-cell formula but applies it to each cell in the range A1

using ARRAYFORMULA.

Using Google Apps Script for Word Counting

For more advanced word-counting needs, Google Apps Script provides a powerful way to automate tasks. Here’s how you can create a custom word count function using Google Apps Script.

Creating a Custom Function

1. Open Script Editor: In your Google Sheet, go to Extensions > Apps Script.
2. Enter the Script: Copy and paste the following script into the editor.

function WORDCOUNT(input) {

  if (typeof input !== ‘string’) return 0;

  input = input.trim();

  if (input === ”) return 0;

  return input.split(/\s+/).length;

}

1. Save the Script: Click the save icon and name your project.
2. Use the Function: In your Google Sheet, use the function =WORDCOUNT(A1) to count the words in cell A1.

Explanation

• Function Definition: The function WORDCOUNT takes a single input.
• Input Check: It first checks if the input is a string.
• Trim Input: Removes any leading or trailing whitespace.
• Word Count: Uses split(/\s+/) to split the string by spaces and counts the resulting array length.

Counting Words in a Range

To count words in a range using Google Apps Script, you can modify the script slightly:

function WORDCOUNT_RANGE(range) {

  var totalWords = 0;

  for (var i = 0; i < range.length; i++) {

    for (var j = 0; j < range[i].length; j++) {

      if (typeof range[i][j] === ‘string’) {

        var words = range[i][j].trim().split(/\s+/);

        if (words[0] !== ”) {

          totalWords += words.length;

        }

      }

    }

  }

  return totalWords;

}

Use this function with an array formula in Google Sheets, like =WORDCOUNT_RANGE(A1:A10).

Advanced Techniques and Tips

Beyond basic formulas and scripts, some other techniques and tips can help you manage word counts more effectively in Google Sheets.

Using Add-ons

Several add-ons can simplify the process of counting words in Google Sheets:

• Word Counter Max: This add-on provides a simple interface to count words, characters, and other text metrics within your sheet.
• Power Tools: A more comprehensive tool that offers a variety of text functions, including word count.

Combining Functions

For more complex tasks, combining multiple Google Sheets functions can be very powerful. Here are a few examples:

Counting Words with Specific Criteria

To count words that meet specific criteria (e.g., words that start with a specific letter), you can use a combination of ARRAYFORMULA, SPLIT, and FILTER functions. Here’s an example formula that counts words starting with “G” in a cell range A1:A10:

=SUM(ARRAYFORMULA(LEN(FILTER(SPLIT(A1:A10,” “), LEFT(SPLIT(A1:A10,” “),1)=”G”))))

Counting Words Excluding Stop Words

Stop words are common words that you might want to exclude from your word count (e.g., “the”, “and”, “is”). Here’s a way to count words excluding stop words using a custom script:

function WORDCOUNT_EXCLUDE(input) {

  var stopWords = [‘the’, ‘and’, ‘is’, ‘in’, ‘at’, ‘of’, ‘a’, ‘to’];

  if (typeof input !== ‘string’) return 0;

  input = input.trim().toLowerCase();

  if (input === ”) return 0;

  var words = input.split(/\s+/);

  var count = 0;

  for (var i = 0; i < words.length; i++) {

    if (stopWords.indexOf(words[i]) === -1) {

      count++;

    }

  }

  return count;

}

Use this function in your Google Sheet as =WORDCOUNT_EXCLUDE(A1).