Word counting in Excel

  • Thread starter Thread starter norrishd833
  • Start date Start date
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)
 
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?
 
Back
Top