count number of words in cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2256428%22%20slang%3D%22en-US%22%3Ecount%20number%20of%20words%20in%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2256428%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20need%20to%20count%20the%20number%20of%20words%20in%20each%20cell.%20Words%20are%20separated%20by%20a%20single%20space.%3C%2FP%3E%3CP%3EI%20use%20Excel%2016.47.1%20for%20Mac%20(Catalina).%3C%2FP%3E%3CP%3EI'm%20using%20this%20formula%2C%20but%20I%20get%20an%20error.%20Can%20I%20have%20some%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(LEN(TRIM(A2))%3D0%2C0%2CLEN(TRIM(A2))-LEN(SUBSTITUTE(A2%2C%22%20%22%2C%22%22))%2B1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EStefania%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2256428%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2256560%22%20slang%3D%22en-US%22%3ERe%3A%20count%20number%20of%20words%20in%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2256560%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1018196%22%20target%3D%22_blank%22%3E%40sspina%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20formula%20looks%20OK.%3C%2FP%3E%0A%3CP%3EDo%20you%20use%20comma%20as%20decimal%20separator%3F%20If%20so%2C%20use%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(LEN(TRIM(A2))%3D0%3B0%3BLEN(TRIM(A2))-LEN(SUBSTITUTE(A2%3B%22%20%22%3B%22%22))%2B1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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!