Saturday, December 04, 2004

MS Excel: Word Count

Here is VBA Macro to count number of words in Excel spreadsheet as MS Excel does not have built in word counter like MS Word:

Sub NumberOfWords()

Dim NumberOfWord As Long
Dim RangeArea As Range
Dim Str As String
Dim Num As Long

For Each RangeArea In ActiveSheet.UsedRange.Cells
Str = Application.WorksheetFunction.Trim(RangeArea.Text)
Num = 0
If Str <> "" Then
Num = Len(Str) - Len(Replace(Str, " ", "")) + 1
End If

NumberOfWord = NumberOfWord + Num

Next RangeArea

MsgBox NumberOfWord

End Sub

1 comment:

jason morin said...

That macro looks a lot like the one found here:

http://www.cpearson.com/excel/WordCount.htm