Count words in Excel

  Neill 16:45 18 Mar 2006
Locked

I'm trying to find a formula that will count just words (text) in a range that also includes numbers. Lets say there are 4 words and 5 numbers mixed up in range E1:E9 =Counta comes up with 9 whilst Count returns 5. Excel Help suggest an array formula =SUM(IF(LEN(TRIM(E1:E9))=0,0,LEN(TRIM(E1:E9))-LEN(SUBSTITUTE(E1:E9," ",""))+1)) where E1:E9 contains text and numbers but it comes up with the answer of 9. I suppose I could use COUNT and COUNTA then take one from another but wondered if there was a way though the suggested formula is a real mouthful. Thanks

  VoG II 16:59 18 Mar 2006

I would use

=COUNTA(E1:E9)-COUNT(E1:E9)

  Neill 17:07 18 Mar 2006

I thought that would probably be the best way. Can't imagine my ECDL students getting to grips with the array formula (let alone me). Just wondered though if there was a function hidden away somewhere. Thanks for the advice.

  Simsy 17:13 18 Mar 2006

You say "Count words"... do you really mean count the NUMBER of words? What if a single cell contains two words.. would that be a count of 2?

Regards,

Simsy

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

Creative studio Omnibus' brand identity for We Said Enough fights back against sexual misconduct

WWDC history: Apple's product launches since 2005

Espace de stockage : comment libérer de la mémoire sur votre iPhone ?