WordCount

WordCount Location: http://www.mvps.org/dmcritchie/excel/wordcnt.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

Count the number of words in a string

WordCount returns 7 in the following example. The word separator is a space (" ").
=WordCount("There are seven words in this sentence."," ")

Function WordCount(Txt, Separator) As Long
'   David McRitchie http://www.mvps.org/dmcritchie/excel/wordcnt.htm
'   Returns a Count of Words in a text string, where the elements
'   are separated by a specified separator character
'   beginning and trailing spaces are eliminated first though.
'   Coding is derived from coding for ExtractElement
'     at http://www.j-walk.com/ss/excel/tips/tip32.htm

    Dim Txt1 As String, temperament As String
    Dim ElementCount As Long, i As Long
    Separator1 = Separator
    If Separator = "" Then Separator1 = " "
    LastTxt1 = Separator1

    Txt1 = Txt
'   If space separator, remove excess spaces
'   If Separator = Chr(32) Then Txt1 = Application.Trim(Txt1)
'   Eliminated beginning and trailing spaces regardless of separator
    Txt1 = Application.Trim(Txt1)

' Add a separator to the end of the string
If Right(Txt1, Len(Txt1)) <> Separator1 Then Txt1 = Txt1 & Separator1

' Initialize
ElementCount = 0

'   Extract each element -- count adjacent separators as one
    For i = 1 To Len(Txt1)
        If Mid(Txt1, i, 1) <> LastTxt1 Then
           If Mid(Txt1, i, 1) = Separator1 Then
               ElementCount = ElementCount + 1
           End If
        End If
        LastTxt1 = Mid(Txt1, i, 1)
    Next i
    WordCount = ElementCount
End Function

In order to use this in step with ExtractElement you must add the coding in red and comment out those in italic to your version of ExtractElement or remove it from your version of WordCount. The object is to treat duplicate separators as one. If the separator is a space then TRIM would have removed all leading and trailing spaces but not embedded spaces.

Please refer to http://www.j-walk.com/ss/excel/tips/tip32.htm for documentation and original coding of ExtractElement. The modified coding appears below.

If you use " " as your separator you might want to surround usage with =TRIM()

Function ExtractElement(Txt, n, Separator) As String
'   Returns the nth element of a text string, where the elements
'   are separated by a specified separator character
'   Original version of ExtractElement coding found
'     at http://www.j-walk.com/ss/excel/tips/tip32.htm
'   Modified version by D.McRitchie eliminates redundant separators
'     in http://www.mvps.org/dmcritchie/excel/wordcnt.htm

Dim Txt1 As String, temperament As String
Dim ElementCount As Long, i As Long

    Txt1 = Txt
'   If space separator, remove excess spaces
'   If Separator = Chr(32) Then Txt1 = Application.Trim(Txt1)
'   Eliminated beginning and trailing spaces regardless of separator
    Txt1 = Application.Trim(Txt1)
    Lastsep = 1

' Add a separator to the end of the string
If Right(Txt1, Len(Txt1)) <> Separator Then Txt1 = Txt1 & Separator

'   Initialize
    ElementCount = 0
    TempElement = ""

'   Extract each element
    For i = 1 To Len(Txt1)
        If Mid(Txt1, i, 1) = Separator Then
            If Lastsep = 1 Then GoTo nexti
            ElementCount = ElementCount + 1
            If ElementCount = n Then
'              Found it, so exit
               ExtractElement = TempElement
               Exit Function
            Else
               TempElement = ""
            End If
        Else
            TempElement = TempElement & Mid(Txt1, i, 1)
           LastSep = 0
        End If
nexti:
    Next i
    ExtractElement = ""
End Function

This page is an extension of one part of Strings and Manipulations

You are one of many distinguished visitors who have visited my site here or in a previous location since this page was created on October 6, 1998.

Visit [my Excel home page] [Index page] [Excel Onsite Search] [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments

Related (#related)