Count words in Excel

  Neill 16:45 18 Mar 06
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 06

I would use

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

  Neill 17:07 18 Mar 06

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 06

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.

Sniper Elite 4 review: Headshotting Nazis has never felt so good

1995-2015: How technology has changed the world in 20 years

The Fresh New Fonts of 2017

WWDC 2017 dates: How to get WWDC 2017 tickets, when is WWDC 2017 and more details announced