Excel has many different functions that can help you parse out text from cells. This includes the LEN, MID, LEFT, and RIGHT functions. By utilizing these and other functions, you can get just the values you want. And by determining the number of blank spaces within a cell, you can also determine the number of words that a cell contains. There are multiple ways you can count cells in Excel, I’ll start with using the easier, and newer TEXTSPLIT function.
Method 1: Counting words using the TEXTSPLIT function
The TEXTSPLIT function is available for users who have Microsoft 365 and so if you do not see that function available as you type it in, you’ll need to move to the second approach. Using the TEXTSPLIT function, you can turn a single text value in a cell into multiple cells or columns. And you can specify how you want to split a cell; which delimiter you want to use.
In the example of counting words, the delimiter you would use is a blank space, as specified with ” ” in the delimiter argument. Here’s a list of article titles that I am going to use for this example:
The article titles are in column A. The formula to split the text every time there is a blank space would be as follows, assuming the first value is in cell A2:
=TEXTSPLIT(A2,” “)
This formula, however, would simply put all the words in different columns. Thus, it is incomplete when your goal is to count the number of words. To fix this, the formula needs to be embedded within the COUNTA function. How COUNTA works is that it simply counts the number of nonblank values.
=COUNTA(TEXTSPLIT(A2,” “))
Copying this formula down, these are the resulting values and the number of words found in each cell:
Here’s a closer look at how the formula in B2 works, using the Evaluate Formula feature in Excel:
The TEXTSPLIT function is breaking out each word as its own separate value. And the COUNTA function is then counting each one of those values. When combined, these functions allow you to count the number of words in a cell.
If you’re using Google Sheets, you can use the exact same formula as shown above, with the only difference being that instead of TEXTSPLIT, you’ll use the SPLIT function. It works in the exact same way.
Method 2: Using the LEN and SUBSTITUTE functions to count words
If you are on an older version of Excel where TEXTSPLIT isn’t available, there’s still a way that you can count the number of words within a cell. It will be a slightly more complex formula that will use the LEN and SUBSTITUTE functions.
The first part of the formula will involve counting the number of characters in a cell, which is what the LEN function does. This is accomplished through the LEN(A2) formula — assuming that A2 is where the article name is.
Next, you’ll need to use the SUBSTITUTE function to replace the blank values ” ” with an empty string that just contains two quotes: “”. To do that, the formula for that portion would be: SUBSTITUTE(A2,” “,””). This formula will need to be enclosed within a LEN function. What this accomplishes is it counts the number of characters in the cell after you’ve replaced all the blank values. If you take the total cell length and subtract this second piece, you’ll be left with the number of blank values in the text.
=LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)
However, this isn’t entirely correct as you will be off by 1 word. This is because since the formula is counting the number of blanks, it won’t include the first word, which doesn’t come with a space before it. That also means if you only have one word, you’ll have a value of 0 instead of 1. To fix this, you’ll simply need to add a +1 to the end of your formula.
=LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)+1
This would mean, however, that even blank cells would return a value of 1. And this would technically be the same problem when using the TEXTSPLIT function as well, since it doesn’t check for blanks, either. To correct this, you can simply add an IF function to check if the value is indeed blank. Here’s how the full formula looks:
=IF(A2=””,””,LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))+1)
This will return nothing if the cell is blank. If the cell isn’t blank, then it will go ahead and perform the rest of the calculation. As mentioned, this IF function can also be added to the start of the TEXTSPLIT function as well.
If you liked this post on How to Count Words in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.