Excel does not provide an in-built function to count words in cells. However, there are methods to achieve this. If you are not using VBA, then you can use the below formula to find the total number of words in a cell.
=LEN(A2)-LEN(SUBSTITUTE(A2, " ","")) + 1
The above formula is written against a string a values in the cell A2. For example, if A2 has a string of values, lets’ say, can have a variety of data, the formula will return a result of 6.
Talking about in-built functions, I have recently shared an article on a similar issue, where I have shown with an example on how to check if a cell or range has numbers using an in-built function called “isNumeric()” in VBA.
Now, lets see how you can do this in VBA.
Add a button (an ActiveX control) on your worksheet and write the below code.
Option Explicit Private Sub CommandButton1_Click() countWords (Worksheets("Sheet1").Range("A2:A10")) End Sub Sub countWords(objRange As Range) Dim cell As Variant Dim element As Variant Dim iCount As Integer Dim iCnt As Long Dim iRow As Long iRow = 2 iCount = 1 For Each cell In objRange For iCnt = 1 To Len(cell.Value) element = Mid(cell, 1, InStr(cell, " ")) If Trim(element) <> "" Then iCount = iCount + 1 objRange.Cells(iRow - 1, 2) = iCount End If cell = Replace(cell, element, "") Next iCnt iRow = iRow + 1 iCount = 1 // RE-ASSIGN THE COUNTER. Next cell End Sub
The button’s click event calls a procedure named countWords(). It takes a parameter in the form of a range. You can extend your range if you want.
The procedure runs two loops. The first loop gets a string value from each row. The second loop reads each element (words and other characters) in the string.
The Mid() function extracts a word from the string and add 1 to the counter (iCount). Finally, I am using the Replace() function to replace the extracted word from the string. The loop runs until it reaches the last word in the string.
That’s it. Hope you find this method useful. However, if you have any other suggestions and a simpler method, you can share it with us here.
Thanks for reading.