Count Words in Excel

Helps to count the total number of words in the sheet.

Author: Rohan Arora
Rohan Arora
Rohan Arora
Investment Banking | Private Equity

Mr. Arora is an experienced private equity investment professional, with experience working across multiple markets. Rohan has a focus in particular on consumer and business services transactions and operational growth. Rohan has also worked at Evercore, where he also spent time in private equity advisory.

Rohan holds a BA (Hons., Scholar) in Economics and Management from Oxford University.

Reviewed By: David Bickerton
David Bickerton
David Bickerton
Asset Management | Financial Analysis

Previously a Portfolio Manager for MDH Investment Management, David has been with the firm for nearly a decade, serving as President since 2015. He has extensive experience in wealth management, investments and portfolio management.

David holds a BS from Miami University in Finance.

Last Updated:January 16, 2024

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.

Data

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

Sheet

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.

Difference
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.

Sheet

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.

Data

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.

Data

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.

Sheet

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.

Sheet

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.

Excel

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.

Tab

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.

Filename

Step 5: In the worksheet, press ALT + F8 to open the Macro dialog box. Click on the RUN command to execute the Macro.

Sheet

Step 6: You will see a pop-up box displaying the total words in the sheet.

Excel

Researched and authored by Mohammad Sharjeel Khan | Linkedin

Reviewed and edited by Rohan Joseph | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources: