While I was going through some of the comments I could find an interesting question and that is the topic of this post. The user was asking for an array formula to split and count words in Google Sheets.

Normally we use two individual functions as a combination to split a sentence in a cell and count the words. They are SPLIT and COUNTA.

But the combination won’t work as an array formula to vertically populate the count of split words in every row.

What’s the reason?

In combination, the COUNTA is not suitable for returning an array result. You can understand it from the below example.

In the above example, I have used two non-array formulas to split and count words in Google Sheets.

You can use the SPLIT in an array as below.

=ArrayFormula(split(A2:A3," "))

If you use the COUNTA you will get the total count of the words, and that would be 15 in the cell B2, not 8 in B2 and 7 in B3.

Array Formula to Split and Count Words (Workaround)

I can offer two different types of formulas as an alternative to split and count words in Google Sheets. The logic is quite different.

Logic 1

The first formula is a Regexreplace based one. It replaces all the words in every row with blanks. The left characters are only white spaces. So, we can get the length of the spaces in each row.

Adding +1 to the length of the spaces will be the count of words.

Logic 2

In the second formula, the logic is entirely different.

Using the Substitute function, I’ll remove the spaces in the sentences (text strings) with blanks. The length_of_actual_words - the length_of_words_with_spaces_removed + 1 will be the count.

How to implement the above two logics as an alternative to split and count words in Google Sheets.

Regex Array Formula to ‘Split’ and Count Words in Every Row in Google Sheets

First, I’ll explain how to regex replace all the non-spaces in a sentence. So we will get spaces only in every cell in the row.

=ArrayFormula(regexreplace(A2:A,"[^\s]",""))

The above Regexreplace array formula in cell B2 won’t display any values as it only returns the spaces from the words in A2:A.

It replaces all the words in A2:A with blanks. So the white spaces are only left. So obviously, we won’t see any values or characters.

Include the LEN function with the above formula to get the length of spaces in every row. Not the split and count of words.

We should add the value 1 to the length of spaces to get the number of words, or we can say split and count words in every row in Google Sheets because the number of words in a sentence will be the number of spaces + 1.

=ArrayFormula(LEN(regexreplace(A2:A,"[^\s]",""))+1)

The above formula is for the entire column A2:A. So it returns 0 in some cells (against blank rows in A2:A). To avoid that, use another IF+LEN as below.

=ArrayFormula(if(len(A2:A),LEN(regexreplace(A2:A,"[^\s]",""))+1,))

The above is the first example (workaround) to an array formula to split and count words in Google Sheets.

Substitute Array Formula as a Workaround to Split and Count Words in Every Row

Even though the above formula will meet your requirement, here is one more formula. The following is the second logic mentioned above.

This time, let me share with you the formula first, and then you can see the explanation.

=ArrayFormula(if(len(A2:A),len(A2:A)-len(SUBSTITUTE(A2:A," ",""))+1,))

Formula Explanation

There are two major parts.

part 1

len(A2:A)

part 2

len(SUBSTITUTE(A2:A," ",""))

The part 1 formula with ArrayFormula would return the actual length (count of characters) of the sentences. Part 2 will first substitute all the spaces with blanks and return the count.

part1-part2+1 will be the formula equivalent to split and count words in Google Sheets.

Similar to our Regex formula, here also to remove 0s against blank cells, I’ve used if(len(A2:A).

That’s all.

Resources:

1. How to Count Comma Separated Words in a Cell in Google Sheets.
2. Sum, Count, Cumulative Sum Comma Separated Values in Google Sheets.
3. Count Words and Insert Equivalent Blank Rows in Google Sheets.
4. Get the Count of Occurrences in Each Row in Google Sheets (Combo Formula).
5. Count a Specific Character in Google Sheets.