Count Words in Excel
Helps to count the total number of words in the sheet.
How to Count Words in Excel
Language is a vital aspect of humans. It is through language we pass information and communicate with each other. Words are the essential elements of language. When put together in a sentence, words help us share with others.
Thus, analyzing and understanding words is essential. Counting words is one way of analyzing written data. Keeping track of the number of words is necessary for writers and professionals in communication services.
When you think about words or writing, Excel is not something that first comes to mind. If you use Microsoft Word a lot, you must be used to the word count function. But unfortunately, Excel does not have a default function to count words.
But don’t worry, we got you covered; below, you will find a way to count words in a cell using a complex set of formulas. The formula was originally not intended for this purpose, but we can use it to count words.
Key Takeaways
- In the formula, select the cell you have your data. We have used the cell with data in our example.
- Remember, the formula to count words will also count punctuation marks like commas and full stops.
- Ctrl+Shift+Enter is necessary to calculate multiple arrays using the SUM function. You will see a #VALUE! Error if you just press Enter.
- The formula to count specific words may count a part of a word if it matches the text. For example, it will count pick as a separate word in a pickle.
- You can also use the SUM function to calculate the number of occurrences of a word in a range of cells.
Excel Word Count Formula
We will use three different functions to count the total number of words in a given cell. But before going any further, it would be beneficial to know what each function does and how they can count words.
=LEN(TRIM(D1))-LEN(SUBSTITUTE(D1," ",""))+1
Where
- LEN: This function returns the length of a string, which means that it will display the total characters (alphabets, numbers, space, punctuation marks) in a cell
- TRIM: This function removes spaces in a text string except for one space between two words
- SUBSTITUTE: This function replaces text with new text in a string
We add 1 to this formula because when we subtract the length of a string with no spaces, we also subtract the space between words. to get the correct result.
We see that the formula correctly calculates the number of words in the cell, which is 2.
Alternate Formula
The formula above is great but has one drawback; It counts a cell with no word as 1. We can use this alternate formula to counter this problem.
=IF(D1="", 0, LEN(TRIM(D1))-LEN(SUBSTITUTE(D1," ",""))+1)
The If function checks if a condition is true or false. We can use the IF function to create complex sets of requirements.
- D1="": The first part is the logic test the function has to check. Here, we want to check if a cell is empty
- 0: The second part is the answer to be displayed if the value is true
- LEN: The third part is the execution of the formula if the value is false
Here, we use it to return 0 if the cell is empty and return the result of the other formula if a word is present. You can see in the example above the empty cell returns a 0 count with this formula.
Counting the Total Words in a Range
In the example above, we saw how to count words in a cell. But if you want to calculate the total number of words in a range of cells, you may find dragging the cell with the formula over each cell cumbersome. You can use the formulas mentioned below to count total words in a range of cells in one click.
=SUMPRODUCT(LEN(TRIM(D1:D3))-LEN(SUBSTITUTE(D1:D3," ",""))+1)
OR
=SUM(LEN(TRIM(D1:D3))-LEN(SUBSTITUTE(D1:D3," ",""))+1)
Some differences between these formulas are listed below; you may choose whichever you find convenient.
SUMPRODUCT | SUM |
---|---|
SUMPRODUCT is an array formula that can handle multiple calculations on more than one item. | The SUM is a regular function that adds values, ranges, or cell references. It is called a CSE function. |
You just need to press Enter to execute the formula. | Since this is a regular function, you must press Ctrl+Shift+Enter to calculate arrays. |
No such brackets are seen, as it is already an array formula. | Curly brackets{} are seen in the formula by excel to show that it is an array formula. |
You can see in the below example that typing the formula in the first cell and dragging it down to execute it to other corresponding cells and then adding the result of all these numbers is tedious and time-consuming. Plus, it is also problematic if the data set is too large.
But by adding the SUMPRODUCT function and selecting the whole range instead of choosing one cell as a reference, we can find the total number of words in just one click.
Specific Words in a Cell
By using the above formulas, we get the total number of words in a cell, but what if we want to know the total number of a ‘specific’ word in a cell? We've got you covered; the formula below will help you count specific words in a cell.
1. Case-Sensitive
We can use a different formula having the same functions to calculate specific words/phrases in a data set. To do this, we must type the word/phrase we want to count.
=(LEN(D1)-LEN(SUBSTITUTE(D1, "Car","")))/LEN("Car")
- (Len(D2):- The formula's first part calculates the text string's total length.
- LEN(SUBSTITUTE(D2, "Car",""))):- The second part calculates the length of the string after removing the word “Car” from the string. This is then subtracted from the first part.
- LEN("Car"):- The third part divides the number of characters in a word from the above result.
In the example below, we see the data of vehicles and use the formula above to calculate the number of cars in the data. We type the formula in cell E1 and drag it down to execute it on other cells.
In the end, we can quickly sum the data to find the total number of cars in our data. You can use this formula on other data sets where you want to count a specific word.
But this formula has one disadvantage, it is case sensitive, i.e., it will not count the same word in a different case than the one specified like it will not count ”car” or “CAR” in the above example.
2. Case-Insensitive
The formula above is case-sensitive, and you may get inaccurate results if the word you want to count is in both lower and uppercase. Don’t worry; we have the formula to counter this problem.
=(LEN(D1)-LEN(SUBSTITUTE(LOWER(D1), LOWER("Car"), "")))/LEN("Car")
OR
=(LEN(D1)-LEN(SUBSTITUTE(UPPER(D1), UPPER("Car"), "")))/LEN("Car")
We will just add a function called Lower that will change the text string of a cell to lowercase, and we will also turn the text car into lowercase to match the data.
The example clearly shows how the case-insensitive formula counts the word no matter what letter case it is and gives the correct answer.
Specific Words in a Range
We have already seen the formula to count specific words in a cell, but what about a whole range of cells? You may want to count how many times a particular word has been typed in your data set.
Yeah, sure, you can drag the formula corresponding to each row and then add the results like in the example above. But It is cumbersome to do so and near impossible if the data is too big.
1. Case-Sensitive
The formula below is similar to counting the number of occurrences of a word in a cell, except for two main differences.
=SUMPRODUCT((LEN(D1:D7)-LEN(SUBSTITUTE(D1:D7, "Car","")))/LEN("Car"))
- SUMPRODUCT: We start the formula with this function, which combines multiple arrays
- The cell reference has a range of cells specified instead of a single cell
The rest of the formula is the same as the one mentioned before.
We have used the same data set as the one used to count specific words in a cell. You can see in the example below that just by one formula, we can calculate the number of occurrences of the word “Car” in our data set.
This is much easier and quicker than dragging the cell with the formula and then adding the results to get the final answer. This makes counting word occurrence in an extensive data set very quick and efficient.
2. Case-Insensitive
The first formula is, as mentioned, case-sensitive. If you know that your data or the word you want to count is in a particular letter case, you may not need a different formula that is case-insensitive. But if your data is an article or an excerpt from a book, you will need a case-insensitive formula.
=SUMPRODUCT((LEN(D1:D7)-LEN(SUBSTITUTE(UPPER(D1:D7), UPPER("Car"),"")))/LEN("Car"))
OR
=SUMPRODUCT((LEN(D1:D7)-LEN(SUBSTITUTE(LOWER(D1:D7), LOWER("Car"),"")))/LEN("Car"))
Using the same data set as an example but with the word in different letter cases, we can see how the first formula can give an incorrect answer and how this formula helps overcome the problem.
VBA Code to Count Words
Visual Basic Analysis is a programming code that is widely used in Excel. Professionals use it to ease their workload through repetitive programming tasks. Macros are what we use to program our tasks in VBA. Macros are a set of actions that can be executed repeatedly.
Step 1: Press ALT + F11 to open the Microsoft Visual Basic for Applications window. You will see a window open like the one in the example below.
Step 2: Click on the Insert tab, and in the drop-down, select module.
Step 3: Copy and Paste the formula below into the module box.
Sub CountWords()
Dim WordCount 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
WordCount = WordCount + N
Next Rng
MsgBox "Words In ActiveSheet: " & Format(WordCount, "#,##0")
End Sub
Step 4: Save the file as an Excel macro-enabled workbook in the Save as type drop-down list and Save. Press ALT + Q to close the VBA editor.
Step 5: In the worksheet, press ALT + F8 to open the Macro dialog box. Click on the RUN command to execute the Macro.
Step 6: You will see a pop-up box displaying the total words in the sheet.
or Want to Sign up with your social account?