General discussion

Locked

Word Count In Excel

By bjjhome.bt ·
Hi All

Has anyone any idea if it is possible to use wordcount in MS Excel.

Thanks

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Word Count In Excel

by FirstPeter In reply to Word Count In Excel

I know you can create your own word counting function pretty quickly, like so:

1. Open the Visual Basic Editor, then add a module to your workbook.
2. In the module, add the following lines of code:


Function Word_Count(strText As String) As Long
Dim i, lngWords As Long

lngWords = 1

' Add one to the word count each time we encounter a space
For i = 1 To Len(strText)
If Mid(strText, i, 1) = " " Then
lngWords = lngWords + 1
End If
Next i

Word_Count = lngWords
End Function


That should give you a word count function for Excel. To use it, go back to Excel and type in some text. Then, in another cell, type in:

=Word_Count(<cell reference you typed text in here&gt

It should return the numberof words. Does that help?

Collapse -

Word Count In Excel

by bjjhome.bt In reply to Word Count In Excel

The module only works on individual cells and not an array of cells. Also, it counts an empty cell as 1 - I assume because it is counting spaces.

Collapse -

Word Count In Excel

by bjjhome.bt In reply to Word Count In Excel

The module only works on individual cells and not an array of cells. Also, it counts an empty cell as 1 - I assume because it is counting spaces.

Collapse -

Word Count In Excel

by bjjhome.bt In reply to Word Count In Excel

The module only works on individual cells and not an array of cells. Also, it counts an empty cell as 1 - I assume because it is counting spaces.

Any further ideas?

Collapse -

Word Count In Excel

by FirstPeter In reply to Word Count In Excel

Try this:

Function Word_Count(strText As String) As Long
Dim i, lngWords As Long

Word_Count = 0
lngWords = 1

' If it's empty, exit the function
If strText = "" Then Exit Function

' Add one to the word count each time we encounter a space
For i = 1 To Len(strText)
If Mid(strText, i, 1) = " " Then
lngWords = lngWords + 1
End If
Next i

Word_Count = lngWords
End Function

Function Word_Count_Range(rngRange As Range) As Long
Dim lngWords As Long
Dim rngCell As Range

lngWords = 0

' Call the Word_Count function for each cell in the range
For Each rngCell In rngRange
lngWords = lngWords + Word_Count(rngCell.Value)
Next

Word_Count_Range = lngWords
End Function

Collapse -

by bjjhome.bt In reply to Word Count In Excel

Poster rated this answer.

Collapse -

by bjjhome.bt In reply to Word Count In Excel

This question was closed by the author

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums