Excel Word Count: Tips and Methods
Excel Word Count: Tips and Methods. Excel doesn’t have a built-in option to count words like in MS Word, but you can create a formula to do it. Here’s how:
Word Count in a Single Cell:
This formula counts the number of spaces in a cell and adds 1 to get the total number of words.
Use a UDF (User-Defined Function):
You can also create a custom function with VBA code:
Function MyWordCount(rng As Range) As Integer
MyWordCount = UBound(Split(rng.Value, ” “), 1) + 1
Then, use the function by entering “=MyWordCount(” followed by the cell reference.
Count Words in a Range of Cells
Let’s take things up a notch and count the words in a range of cells instead of just one. The good news is that you only need to make a slight modification to the formula you used before. Here’s the updated formula:
In this formula, A1:A11 represents the range of cells you want to count. When you use this formula, it will return a count of 77 words.
Here’s how it works: Like the previous method, this formula counts the number of spaces between words and adds 1 to the result to get the total number of words. The only difference is that we’re using SUMPRODUCT to handle arrays of cell values instead of a single cell.
Count Words in the Entire Worksheet
This macro code is a useful tool that I frequently use in my work to count all the words in a worksheet. Here’s the code:
Dim WordCnt As Long
Dim rng As Range
Dim S As String
Dim N As Long
For Each rng In ActiveSheet.UsedRange.Cells
S = Application.WorksheetFunction.Trim(rng.Text)
N = 0
If S <> vbNullString Then
N = Len(S) – Len(Replace(S, ” “, “”)) + 1
WordCnt = WordCnt + N
MsgBox “There are a total of ” & Format(WordCnt, “#,##0″) & ” words in the active worksheet.”
When you run this code, it will display a message box that shows the number of words in the active worksheet.
Count a Specific Word/String in a Range
In this scenario, you need to count the number of occurrences of a specific word or text string in a range of cells. For example, let’s say you want to count the number of times the word “Monday” appears in a range of four cells. Here’s the formula you can use:
When you use this formula, it will return a count of 4 for the word “Monday”.
Please note that this formula counts the number of times the word appears in the range, not the number of cells that contain the word. In this example, the word “Monday” appears four times in three cells.
Here’s how it works: The formula uses four parts. The first part calculates the length of each cell in the range. The second part calculates the length of each cell with the specified word removed. The third part calculates the length of the specified word. Finally, the fourth part subtracts the second part from the first part and divides it by the third part. This results in an array with the count of the specified word in each cell. SUMPRODUCT is used to sum this array and return the total count of the specified word in the range.
In conclusion, these methods are helpful for keeping track of word counts in Excel. Although Excel doesn’t have a built-in word count feature, these methods provide a useful workaround. Do you have a favorite method? Please share your thoughts in the comments section. And if you found this post helpful, please share it with your friends.