Counting Words

Hello Allen,

For counting words, you can use this formula (the cell to test is in F5)

=LEN(F5)-LEN(SUBSTITUTE(F5," ",""))+1

If you consider the words are separated by a space...

Kind Regards

Jean-Paul BERTUOL
(from FRANCE)

@Thomas Papavasiliou
Thanks for the compliment.
In the following macro I reversed the Trim and Replace functions so the new line characters Chr(10) are replaced with a space first and then the Trim function replaces the double spaces with only one. This corrects the count in the case of a new line followed by a space.

Sub CountWords()
Dim cell As Range, Raw As Variant, X As Integer
For Each cell In Selection
If Not cell.HasFormula Then
X = X + UBound(Split(Application.Trim(Replace(cell, Chr(10), " ")))) + 1
End If
Next cell
MsgBox "There are " & X & " words in the selection."
End Sub

As for your second request (limit rows), years ago I developed a UDF that does just that:

Private Function AdjustedSelection() As Range
Dim Area As Range, Lcc As Integer, Lcr As Long, C As Integer, R As Long
Set AdjustedSelection = Selection.Cells(1) '}
Lcc = Selection.SpecialCells(xlLastCell).Column '}initialize
Lcr = Selection.SpecialCells(xlLastCell).Row '}
For Each Area In Selection.Areas 'loop through all selected areas
R = Area.Rows(Area.Rows.Count).Row 'area's bottommost row
If Lcr < R Then R = Lcr 'if LastCell's row is less: adjust
C = Area.Columns(Area.Columns.Count).Column 'area's rightmost column
If Lcc < C Then C = Lcc 'if LastCell's column is less: adjust
Set AdjustedSelection = Union(AdjustedSelection, Range(Area.Cells(1), Cells(R, C)))
Next Area
End Function

To use it just replace 'For Each cell In Selection' with 'For Each cell In AdjustedSelection'.
On my website (http://wv-be.com/Ribbon.asp?real-life) is explained in detail how it works.

The macro proposed 13 Aug 2016, by Willy Vanhaelen is brilliant and very fast.

Following a couple of runs I think that two minor improvements would make it even better.

The improvements are:
• Process cases where we have a sequence of Chr(10) and space that gives an erroneous result.
• Limit the rows to process in cases where user selects an entire column. Selecting an entire column is handy if we have data exceeding several screens and containing empty cells.

Regardless of my remarks, I repeat that the macro is brilliant.

I will be very happy if Willy Vanhaelen proposes a new version.

If there is a line wrap there is not necessarily a new word. Suppose | represents a newline char(10). If a phrase is "The Trumpire State Building" and it is wrapped like "The Trump|ire State Building" then the number of words is unchanged = 4. It does not become 5.
Regards
Brian

UDF (User Defined Function)
Function words(where As Range) As Long
Dim what As Range
With Application
For Each what In where
words = words + .Max(, UBound(Split(.Trim(what.Text), " "))) - (Len(what) > 0)
Next what
End With
End Function
Regards
Brian

Here is a single formula to count the number of words in any range named say rng
=SUMPRODUCT(N(LEN(RNG)>1)+LEN(TRIM(RNG))-LEN(SUBSTITUTE(RNG," ","")))
Regards
Brian

Hi,

I believe it can be done with formulas:

=(INT((SUMPRODUCT(LEN(TRIM(A1:A100)))-SUMPRODUCT(LEN(SUBSTITUTE(TRIM(A1:A100)," ",""))))/COUNTA(A1:A100))+1)*COUNTA(A1:A100)+MOD((SUMPRODUCT(LEN(TRIM(A1:A100)))-SUMPRODUCT(LEN(SUBSTITUTE(TRIM(A1:A100)," ","")))),COUNTA(A1:A100))

The macro in this tip is again way to complicated and it has a bug. When some cells contain wrapped text the word count is incorrect.

My mini-macro deals with it:

Sub CountWords()
Dim cell As Range, Raw As Variant, TotalWords As Integer
For Each cell In Selection
If Not cell.HasFormula Then
Raw = Split(Replace(Application.Trim(cell), Chr(10), " "))
TotalWords = TotalWords + UBound(Raw) + 1
End If
Next cell
MsgBox "There are " & TotalWords & " words in the selection."
End Sub

"Simplicity is the ultimate sofistication (Leonardo da Vinci)"