count number of words in cell

Copper Contributor

Hello,

I need to count the number of words in each cell. Words are separated by a single space.

I use Excel 16.47.1 for Mac (Catalina).

I'm using this formula, but I get an error. Can I have some help?

 

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1)

 

Thank you,

Stefania

2 Replies

@sspina 

The formula looks OK.

Do you use comma as decimal separator? If so, use

 

=IF(LEN(TRIM(A2))=0;0;LEN(TRIM(A2))-LEN(SUBSTITUTE(A2;" ";""))+1)

 

@Hans Vogelaar 

 

Thank you, it works! :)