Word counting in Excel

[norrishd833]

Can anyone much brighter than me tell me how to get the formula detaile
on this site to work:

http://www.pcmag.com/article2/0,4149,1191766,00.asp

Any help gratefully received

 

[Bob Phillips]

It's got spaces after a couple of the SUBSTITUTE functions. Try this

=IF(ISBLANK(A1),0,LEN(TRIM(SUBSTITUTE(A1,CHAR(10),"
")))-LEN(SUBSTITUTE(SUBSTITUTE(TRIM(A1),CHAR(10),"")," ",""))+1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Peo Sjoblom]

It can be shortened to

=LEN(TRIM(A1))-LEN(TRIM(SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)," "),"
","")))+(A1<>"")


I don't see the first

LEN(TRIM(SUBSTITUTE(A1,CHAR(10)," ")))

as necessary, it is just to guard if someone puts a space and then a line
break
but who would do that?