How to count the number of words in a Microsoft Excel file?

Question

I need to count the total number of words in a MS Excel file. Usually, in MS Word or PowerPoint, it's either shown at the status bar or properties window. But in Excel, it's neither given anywhere.

Is there a solution?

Answer 1

You can create a macro for this:

Press ALT + F11 and Enter the below code.

Then Select the whole sheet run the Macro for a word count. You cna also Just select a section and Word Count Just that section.

Sub CountWords()
Dim MyRange As Range
Dim CellCount As Long
Dim TotalWords As Long
Dim NumWords As Integer
Dim Raw As String

Set MyRange = ActiveSheet.Range(ActiveWindow.Selection.Address)
TotalWords = 0
For CellCount = 1 To MyRange.Cells.Count
If Not MyRange.Cells(CellCount).HasFormula Then
Raw = MyRange.Cells(CellCount).Value
Raw = Trim(Raw)
If Len(Raw) > 0 Then
NumWords = 1
Else
NumWords = 0
End If
While InStr(Raw, " ") > 0
Raw = Mid(Raw, InStr(Raw, " "))
Raw = Trim(Raw)
NumWords = NumWords + 1
Wend
TotalWords = TotalWords + NumWords
End If
Next CellCount
MsgBox "There are " & TotalWords & " words in the selection."
End Sub

Answer 2

Try a formula like this:

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