# 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

iMac Pro review

Visual Trends 2018: This year’s must-know colour, design, branding & photography trends

iMac Pro review

Apple Music : comment obtenir 3 mois gratuits ?