Count Words in Cell with Excel VBA

VBA Code to Count Words in a Cell

1. Open Excel and press Alt + F11 to open the VBA editor.
2. In the VBA editor, go to Insert > Module to add a new module.
3. Copy and paste the following code into the module.

Code:

Function CountWords(rng As Range) As Long
    Dim text As String
    Dim words() As String
    Dim i As Long
    Dim wordCount As Long   
    ' Check if the cell is empty
    If IsEmpty(rng.Value) Then
        CountWords = 0
        Exit Function
    End If   
    ' Get the text from the cell and remove leading/trailing spaces
    text = Trim(rng.Value)   
    ' Replace multiple spaces with a single space
    text = Application.WorksheetFunction.Trim(text)   
    ' Split the text into words using space as delimiter
    words = Split(text, " ")   
    ' Count the number of words
    wordCount = 0
    For i = LBound(words) To UBound(words)
        If Len(Trim(words(i))) > 0 Then
            wordCount = wordCount + 1
        End If
    Next i   
    ' Return the word count
    CountWords = wordCount
End Function

Explanation of the Code:

1. Variable Declarations:
   • text: Stores the text from the cell.
   • words(): An array that will hold the words separated by spaces.
   • i: A variable to loop through the words array.
   • wordCount: A counter that will keep track of the number of words.
2. Check if the Cell is Empty:
   • If IsEmpty(rng.Value) Then checks if the cell is empty. If it is, the function returns 0.
3. Text Processing:
   • text = Trim(rng.Value) removes any leading or trailing spaces from the cell’s text.
   • text = Application.WorksheetFunction.Trim(text) removes any extra spaces between words, leaving only a single space between them.
4. Splitting the Text into Words:
   • words = Split(text,  » « ) splits the text into an array of words using space as the delimiter.
5. Counting the Words:
   • The For loop iterates through the words array.
   • If Len(Trim(words(i))) > 0 Then ensures that any empty strings (caused by extra spaces) are not counted.
   • If the word is non-empty, the wordCount is incremented.
6. Returning the Word Count:
   • The function returns the wordCount, which is the total number of words in the cell.

How to Use in Excel:

1. Close the VBA editor by pressing Alt + Q.
2. In any Excel cell, you can now use the CountWords function. For example, to count the words in cell A1, use the formula:

=CountWords(A1)

1. The result will be the number of words in cell A1.

Example:

• If A1 contains the text « Hello there, how are you? », the function will return 5 because there are 5 words.