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:

Excel Word Count: Tips and Methods

=LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))+1

Excel Word Count: Tips and Methods

This formula counts the number of spaces in a cell and adds 1 to get the total number of words.

Excel Word Count: Tips and Methods

Use a UDF (User-Defined Function):

Excel Word Count: Tips and Methods

You can also create a custom function with VBA code:

Excel Word Count: Tips and Methods

Function MyWordCount(rng As Range) As Integer

MyWordCount = UBound(Split(rng.Value, ” “), 1) + 1

End Function

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:

Excel Word Count: Tips and Methods

=SUMPRODUCT(LEN(A1:A11)-LEN(SUBSTITUTE(A1:A11,” “,””))+1)

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:

Sub Word_Count_Worksheet()

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

End If

WordCnt = WordCnt + N

Next rng

MsgBox “There are a total of ” & Format(WordCnt, “#,##0″) & ” words in the active worksheet.”

End Sub

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:

=SUMPRODUCT((LEN(D6:D9)-LEN(SUBSTITUTE(D6:D9,”Monday”,””)))/LEN(“Monday”))

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.