In this post, you’ll learn how to count words in your excel spreadsheet using Excel VBA.
Count Words in Spreadsheet in Excel VBA
To count words in a spreadsheet,
First, insert a command button then add the following codes:
Code:
Private Sub CommandButton1_Click()
Dim sheet As Range, cell As Range
Dim txtinCell, wordsinRange As Integer, text As String
Set sheet = Selection
txtinCell = 0
wordsinRange = 0
For Each cell In sheet
If Not cell.HasFormula Then
text = cell.Value
text = Trim(text)
If text = "" Then
txtinCell = 0
Else
txtinCell = 1
End If
Do While InStr(text, " ") > 0
text = Mid(text, InStr(text, " "))
text = Trim(text)
txtinCell = txtinCell + 1
Loop
wordsinRange = wordsinRange + txtinCell
End If
Next cell
MsgBox " Total No. of words found =" & wordsinRange
End Sub
