Having an excel column as below:

I need to find out the number of words in the column.

CodePudding user response：

Try below formula-

=LEN(A2)-LEN(SUBSTITUTE(A2,",","")) 1

For dynamic spill array.

=MAP(A2:A5,LAMBDA(x,LEN(x)-LEN(SUBSTITUTE(x,",","")) 1))

CodePudding user response：

If you have TEXTSPLIT:

=COUNTA(TEXTSPLIT(A1,","))