Google Sheets is an awesome website that has become the alternative website for data entry to Microsoft Excel. Although it’s mostly related to statistics, formulas and calculations, keeping tabs on the number of words written is also important.
Unfortunately, Google sheets do not have an in-built tool for counting the number of words written on your worksheet but there are ways to go about it using combinations of functions and formulas.
This is not as convenient as Google Docs but it is straightforward to understand. In this tutorial, we would show you how to use the functions to count words in different ways on your Google sheet.
How to Get the Total Word Count in Google sheets.
If you want to get the total word count in your Google sheets, there are two methods you can use to achieve this.
- Using the LENS function.
- Using the COUNTA and SPLIT functions.
Method 1: Using the LENS function.
The LEN function is one of the basic functions in Google sheets. This function is used to count the length of a word.
It is also used to count special characters, letters, punctuation marks and spaces in a sentence written on a Google sheet. Using the short and basic formula below, we can count the total number of characters and spaces in a selected text.
=LEN(cell reference)
Step 1: Select the cell reference containing the text you want to count.
Step 2: In Column B, type in the LENS formula with the selected cell reference.
Step 3: Click Enter. The number of characters and letters in A2 is counted and displayed in the result column B.
Step 4: Double click or drag down the formula to auto-fill the character count of the other cells into column B.
Step 5: Click Enter and the respective character count of the other cells are displayed in column B.
Method 2: Using the COUNTA and SPLIT functions.
The COUNTA function is used to count the number of values in a worksheet while the SPLIT function is responsible for counting the words separated by a space in the cell.
This is also an easy method to count total words in a sheet by using the combination formula below;
=IF(cell reference=” “, ” “, COUNTA(SPLIT(cell reference, ” “)))
Step 1: Select cell A2 as the cell reference in your formula.
Step 2: In B2, type in the COUNTA and SPLIT combination formula.
Step 3: Click Enter. The number of words in A2 is counted and displayed in column B.
Step 4: Double click or drag down the formula to auto-fill the word count of the other cells into column B. The total word count of the cells in column A is displayed in column B.
Unlike the LEN function, the COUNTA and SPLIT combination formula gives the accurate number of words written in the cell.
How to Count an Entire Column in Google Sheets.
If you want to get the total word count of sentences written in an entire column, you use the formula below,
=ARRAYFORMULA(SUM(COUNTA(SPLIT(cell range,” “))))
An array formula is used to describe the total sum of words in the selected range in the column selected.
Step 1: Select the range of words in column A you want to count, that is A2 to A6.
Step 2: In the function tab, type in the array formula and the range of the cells.
Step 3: Click Enter. The total word count of the entire column is produced in column B.
How to Get the Word Count With or Without Specific Words.
When you have texts in your worksheet and you want to find out the number of occurrences of the word “IS”, you’d make use of the formula below.
=IF(cell reference=” “,” “, COUNTIF(SPLIT(cell reference,” “), ” is”))
The split function is to get a list of each word that is separated by a space. The COUNTIF function counts the words that match the criteria word in the formula, “IS”.
If you also want to find the word count in the column without the word “IS”, the formula below is used.
=IF(cell reference=” “, ” “, COUNTIF(SPLIT(cell reference, ” “), “<>is”))))
Step 1: Select the cell reference, A2.
Step 2: Input either of the two formulas in the fx tab, depending on what you want to achieve.
I want to know the number of occurrences of the word “is” so I chose the first formula.
=IF(cell reference=” “,” “, COUNTIF(SPLIT(cell reference,” “), ” is”))
Step 3: Press Enter. The number of occurrences of “is” is displayed in B2.
Step 4: Select the remaining cells in column A and double click on them to produce the number of occurrences of the word “is” in the cell references.
Note: You can find the repetitions of any word in your text.
How to Count Words of Specific lengths.
Counting words above a particular length can also be done in Google sheets. If you want to count words that are four or greater than four letters long, this would require the formula below.
=ARRAYFORMULA(COUNTIF(LEN(SPLIT(cell reference, ” “)), “>4”))
The SPLIT function gets the words separated by a space character. The LEN function finds out the length of each word in the cell and the COUNTIF function counts the lengths of each word. To utilize this formula properly, follow the steps below.
Step 1: Select the cell reference containing the words, that is A2.
Step 2: Input the formula into the function tab.
Step 3: Click Enter. The number of words that are above the criteria length is expressed in column B.
Step 4: Drag down the cursor or double click to automatically provide results for the other rows in column A on your worksheet.
Step 5: Click Enter, the words above the criterion length are listed in column B.
How to Count the Number of Lines in a text in a cell.
Here, we want to count the lines in each row. Each line is separated by a line break, this means we have to count the number of line breaks in each cell.
This requires a combination formula that comprises the SPLIT, COUNTA and CHAR functions.
=COUNTA(SPLIT(cell reference, CHAR(10)))
The sample worksheet below shows the different home addresses in a particular residential area. These addresses are separated by line breaks. We would show you how to count the lines in each text.
Step 1: Select the cell reference containing an address which you want to count its line breaks.
Step 2: Input the formula in the result cell, B2.
Step 3: Click Enter. The number of lines in the text is solved and displayed in the result column.
Step 4: Select the remaining texts in the columns, drag your cursor down to copy the formulas and auto-fill the results into the columns.
Step 5: Click Enter. The line breaks in each cell in column A are counted and displayed in column B.
Online Tools are used to get Word Counts.
The above formulas are very effective when counting the number of words written but there are also alternative ways that are fast and equally as effective as the formulas used above.
1. You can also copy your text typed in your Google sheet.
2. Paste the copied text in Google Docs.
3. Click on the Word Count tool in Google docs.
4. The words are counted by the tool and are displayed as shown below.
Closing Thoughts:
Although Google sheets don’t have an inbuilt counting tool like Google docs, with the formulas provided in this tutorial, word counting has been made easy and flexible for its users to achieve.
The formulas may seem voluminous and complex at first, but with the right amount of time and practice, one would be able to perfect their skills and would be able to count words of large sets of data in Google sheets effectively. Thanks for reading.
