Chip,

This is very cool! Is there a way to set this up for "active cell" only? I
am doing an Request for Proposal response with a 100 word per answer limit. I
suspect our customer is using Excel to align question responses in columns
form multiple vendors. I want to make sure none of my responses exceed the
limit but would rather not count by hand or cut and paste each question (78)
into word.

Thanks in advance.

Paul

"Chip Pearson" wrote:

Excel doesn't have a built-in word counter. You'd have to loop
through each cell, determine how many words in each cell, and sum
these. For example,

Sub AAA()
Dim WordCount As Long
Dim Rng As Range
Dim S As String
Dim N As Long
For Each Rng In ActiveSheet.UsedRange.Cells
S = Rng.Text
N = 0
If S < "" Then
N = Len(S) - Len(Replace(S, " ", "")) + 1
End If
WordCount = WordCount + N
Next Rng
MsgBox WordCount
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Nadine" wrote in message
...
I am translating and need a word count.

You could use a worksheet formula in a cell right next to your input cell to see
the results for that cell:

Something like:

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

====
But you could change one line in Chip's routine to work against the activecell:

For Each Rng In ActiveSheet.UsedRange.Cells
becomes
For Each Rng In ActiveCell.Cells


PGiessler wrote:

Chip,

This is very cool! Is there a way to set this up for "active cell" only? I
am doing an Request for Proposal response with a 100 word per answer limit. I
suspect our customer is using Excel to align question responses in columns
form multiple vendors. I want to make sure none of my responses exceed the
limit but would rather not count by hand or cut and paste each question (78)
into word.

Thanks in advance.

Paul

"Chip Pearson" wrote:

Excel doesn't have a built-in word counter. You'd have to loop
through each cell, determine how many words in each cell, and sum
these. For example,

Sub AAA()
Dim WordCount As Long
Dim Rng As Range
Dim S As String
Dim N As Long
For Each Rng In ActiveSheet.UsedRange.Cells
S = Rng.Text
N = 0
If S < "" Then
N = Len(S) - Len(Replace(S, " ", "")) + 1
End If
WordCount = WordCount + N
Next Rng
MsgBox WordCount
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Nadine" wrote in message
...
I am translating and need a word count.




--

Dave Peterson

Here is a free tool to count words in Excel files:

http://www.montereylanguages.com/exc...free-tool.html

It counts the number of words in Excel files, also gives the option to exclude numbers (like years or dollar amounts) from the word count.

THANKS! VERY USEFUL INDEED