Count words in a cell 

How can we count the num­ber of words in a cell?

We can count the num­ber of spaces between words. Let’s have a look at our first exam­ple. In cell A2, we have spaces between words and at the end of each line, so the case is sim­ple. We can use the LEN func­tion here that counts the length of text (Fig. 1).

=LEN(A2)

Now, that we have the num­ber of signs, we can remove spaces. We can use the SUBSTITUTE func­tion here. This func­tion will change the old text, which in our case is a space into new text. And since we want to remove spaces, our new text will be an emp­ty text string writ­ten in dou­ble quotes (Fig. 2). 

=SUBSTITUTE(A2,” “,“”)

We can see that the func­tion removed all spaces from our text (Fig. 3). 

Now, we can count the length of the text with­out spaces using the LEN func­tion (Fig.4).

=LEN(SUBSTITUTE(A2,” “,“”)

Now we know the length (Fig. 5).

Since we know how many spaces there are, we can use the for­mu­la to count the words. We just have to sub­tract this for­mu­la from the pre­vi­ous one (Fig. 6). 

=LEN(A2)-LEN(SUBSTITUTE(A2,” “,“”))

And we have the num­ber of spaces (Fig. 7).

We still have to remem­ber that to count the num­ber of words, we have to add 1 because there is only one space between two words (Fig. 8).

=LEN(A2)-LEN(SUBSTITUTE(A2,” “,“”))+1

Now we know the num­ber of words in each cell. 

If we want to count words from all cells, we can use almost the same for­mu­la. The only thing we have to change is the range which now will be from A2 to A4 (Fig. 10). 

=LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4,” “,“”))+1

In Dynam­ic Array Excel, the results will be spilled, how­ev­er we can sum the up (Fig. 11).

In the pre­vi­ous ver­sion of Excel we can use the the SUMPRODUCT func­tion (Fig. 12). 

=SUMPRODUCT(LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4,” “,“”))+1

Final results (Fig. 13).

https://www.youtube.com/watch?v=ywuJZ8u_hDA