How to tally words in Excel?

  • Thread starter Thread starter Guest
  • Start date Start date
Count the spaces; add one (1)

=1+LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))
--ron
 
Hi

Here's a VBA approach:

Function CountWordsCell(s As String)

Dim a As Variant

a = Split(Trim(s))

CountWordsCell = UBound(a) + 1

End Function

Paste this function into a new module.

You can then use this function anywhere in your sheet.

You use it like:

=CountWordsCell(C7)
 
But to avoid returning 1 when the cell is empty:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+(TRIM(A1)<>"")
 
See the inline comment....
Here's a VBA approach:

Function CountWordsCell(s As String)

Dim a As Variant

a = Split(Trim(s))
Click to expand...

VBA's Trim function does not work like the spreadsheet formula's TRIM
function... it only removes external blanks.. multiple internal blanks will
remain. So, the above line will not reduce consecutive blank spaces to a
single blank; hence, your count would be incorrect in that situation.
Replace the above line with this...

a = s
Do While InStr(a, " ")
a = Replace(a, " ", " ")
Loop

Rick
 
VBA's Trim function does not work like the spreadsheet formula's TRIM
function... it only removes external blanks.. multiple internal blanks will
remain. So, the above line will not reduce consecutive blank spaces to a
single blank; hence, your count would be incorrect in that situation.
Replace the above line with this...

a = s
Do While InStr(a, " ")
a = Replace(a, " ", " ")
Loop

Rick
Click to expand...

Rick,

Instead of the loop, one could also use:

a = application.worksheetfunction.trim(a)


--ron
 
VBA's Trim function does not work like the spreadsheet formula's TRIM
Rick,

Instead of the loop, one could also use:

a = application.worksheetfunction.trim(a)
Click to expand...

That's true... having been (and still am) a compiled VB person for more than
10 years now, I tend to think of solutions in terms of pure language code,
hence the loop. I have often wondered, though, is there a time (or
efficiency) penalty of any kind to pay for using
Application.WorkSheetFunction? I'm thinking since there is a time penalty to
pay when the spreadsheet world reaches into the VBA world for a solution,
that there is probably one to when going in the reciprocal direction too. Do
you know if that is the case?

Rick
 
That's true... having been (and still am) a compiled VB person for more than
10 years now, I tend to think of solutions in terms of pure language code,
hence the loop. I have often wondered, though, is there a time (or
efficiency) penalty of any kind to pay for using
Application.WorkSheetFunction? I'm thinking since there is a time penalty to
pay when the spreadsheet world reaches into the VBA world for a solution,
that there is probably one to when going in the reciprocal direction too. Do
you know if that is the case?
Click to expand...

I don't know.
--ron
 
Back
Top