February 2021 – LearnYouAndMe

In this article I will show you who you can count number of words present in Excel cell or string. I will show you two solutions for counting number of words in a string.

Also how to count number of words when separator or delimiter is non-space character like dash or underscore Etc for separating words.

1st method uses combination of inbuilt Excel functions to count words.

2nd method uses Excel VBA UDF for counting words in a string present in a cell.

Count words in a cell using Excel functions

In the below image if you carefully look at strings of cells then you will find one pattern, number of words in a string is 1 more than the number of spaces.

So, question arises how to count spaces (single spaces) in a string?

If you just subtract a string length with space with length of same string without space then you will get total spaces in a string.

cell B2 formula is =LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,” “,””))+1

Here LEN(TRIM(A2)) counts the length of a string with space. Trim is applied so that extra spaces (more than one ) get replaced with single space and not applying trim will lead to incorrect output if extra spaces are there in a string.

LEN(SUBSTITUTE(A2,” “,””)) this will output string length without any space. Substitute function is used to replace all the instances of a space in a given string.

Now LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,” “,””)) will output count of spaces in a string. So, just add 1 to count number of words in a String.

Number of words in a string when separator is non-space character

Using the same approach as explained above you can count number of words in cell when delimiter or separator is non-space character like dash(-) or underscore (_).

cell B9 formula =LEN(TRIM(A9))-LEN(SUBSTITUTE(A9,”-“,””))+1

All things are same except substitute text in Substitute function. Here were are replace dash/hypen (-) with no string, since words are separated by hypen (-).

Count words in a cell using Excel VBA UDF

To create below wordCount VBA UDF press ALT+F11 to open VBA IDE. In VBA IDE go to Insert tab and click on Module to create new standard module. Now copy paste below code section in that module.

Make sure to save as your workbook either in .xlsb (binary) or .xlsm (macro) format.

Function wordCount(str As Variant, Optional deli As Variant = " ") As Long
  Dim wrdAr As Variant, tStr As String
  tStr = Application.WorksheetFunction.Trim(str)
  wrdAr = Split(tStr, deli)
  wordCount = UBound(wrdAr) + 1
End Function

After copy pasting UDF in Module you are ready to use wordCount VBA UDF function in Excel cell.

=wordCount (string,[delimiter])

cell B2 formula =wordCount(A2)

cell B9 formula =wordCount(A9,”-“)

Word count VBA UDF takes two inputs

1st is cell having string

2nd is optional which represents delimiter between words. By default 2nd input is single space.

You can also watch the below video to Count number of words in a cell in Excel (word count in a text) (Hindi Speech)

Count specific words or text of a string in cell

Using Excel function to count specific word in a cell

Cell C2 formula =(LEN(A2)-LEN(SUBSTITUTE(A2,B2,””)))/LEN(B2)

Basic idea behind working of specific word count formula in a cell is to find the total length of all the instances that specific word in a string.

After finding total length of all the instances just divide it by the length of that unique word to get the specific word count in a string.

Refer the below image to know working of the formula.

Count specific word in a cell using Excel VBA UDF

This Excel VBA UDF name is wordCountSpl. This VBA UDF is very flixible can count specific word in a string based on whole match or partial match, based on different delimiters and also can count number of words in a string.

Create Standard module as explained in the above section and paste the below code section to use wordCountSpl VBA UDF in Excel.

Function wordCountSpl(str As Variant, Optional wrd As String = "", _
Optional deli As Variant = " ", Optional wholeMatch As Boolean = True) As Long

Dim wrdAr As Variant, tStr As String, val As Variant, cnt As Long

tStr = Application.WorksheetFunction.Trim(str)
If deli <> " " Then
    tStr = Replace(tStr, deli, " ", compare:=vbTextCompare)
End If
wrdAr = Split(tStr, " ")
 
 If wrd = "" Then
    wordCountSpl = UBound(wrdAr) + 1
    Exit Function
 End If
 
 If wholeMatch Then
    For Each val In wrdAr
       If val = wrd Then
          cnt = cnt + 1
       End If
    Next val
    wordCountSpl = cnt
 Else
    wordCountSpl = (Len(tStr) - _
    Len(Replace(tStr, wrd, "", compare:=vbTextCompare))) / Len(wrd)
 End If
End Function

wordCountSpl VBA UDF takes 4 inputs

=wordCountSpl (string, [specific_word] , [delimiter] , [wholeMatch])

1st input is mandatory rest 3 inputs are optional.

1st input is a string

2nd ipnut [optional] is specific word whose count you want to get. If not supplied then UDF counts all the words of a given string.

3rd input [optional] is delimiter used for word separation. By default delimiter is space.

4th input [optional] (True or False) is whether you want to perform whole match or partial match. Default value is whole match (true). Put False if you want to perform partial match.

In the below image 5th and 6th row examples are based on partial match count.

In third row example specific word is not supplied hence outputting all the words count.