Creative Blog

Étiquette : count

  • 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.