On Sat, 11 Aug 2007 06:46:00 -0700, Looking for answers <Looking for
wrote:



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)


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Ron Rosenfeld" wrote:

On Sat, 11 Aug 2007 06:46:00 -0700, Looking for answers <Looking for
wrote:



Count the spaces; add one (1)

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

But to avoid returning 1 when the cell is empty:

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

Ron Rosenfeld wrote:

On Sat, 11 Aug 2007 06:46:00 -0700, Looking for answers <Looking for
wrote:

Count the spaces; add one (1)

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

--

Dave Peterson

On Sat, 11 Aug 2007 19:15:49 -0500, Dave Peterson
wrote:

But to avoid returning 1 when the cell is empty:

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

Good point
--ron

See the inline comment....

Here's a VBA approach:

Function CountWordsCell(s As String)

Dim a As Variant

a = Split(Trim(s))

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




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)


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Ron Rosenfeld" wrote:

On Sat, 11 Aug 2007 06:46:00 -0700, Looking for answers <Looking for
wrote:



Count the spaces; add one (1)

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

On Sun, 12 Aug 2007 04:19:08 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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

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
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

Rick,

Instead of the loop, one could also use:

a = application.worksheetfunction.trim(a)

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

On Sun, 12 Aug 2007 10:27:44 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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?

I don't know.
--ron