How to Count Words in Excel
Last Updated :
06 Dec, 2023
Excel is a tool for storing, organizing, and visualizing a large volume of data. It uses a rectangular block known as a cell to store the data unit. This tool can be used to perform different tasks like creating graphs and analyzing trends, to get insights from the data. Data analysis can be performed in many ways like visual analysis using graphs, mathematical or trend analysis using formulas, etc. It is primarily used for financial data analysis by accounting professionals but can be used by everyone, as it is very easy to use and saves a lot of time. Let's see how to count the number of words in a cell or range of cells.
Unfortunately, Excel doesn't come with a built-in utility for word counts. Fortunately, you can create more complex formulas to perform almost any job by combining a number of functions. And we'll apply this strategy to Excel's word count.
How to Check the Number of Words in a Cell
Let's consider a scenario to count the total number of words present in a cell. As Excel doesn't come with a built-in tool that we could directly use to count the words in a cell, We'll be using different steps to achieve the desired outcome, by combining different formulas. Below are the steps we are going to follow:
- Firstly, we will remove any extra spaces present at the start or at the end of sentences or text data present inside the cell.
- Calculate the total number of characters present, including the spaces present in between the words.
- Now, we will replace all the white spaces that are present in between words and then count the characters.
- Calculate the difference between the total count of characters with spaces to that without spaces (step 2 - step 3). This will give the count of the total number of white spaces.
- Add 1 to the difference to get the count of words.
For demonstration, we will take one string- "Hello!, welcome to GeeksforGeeks." and calculate the number of words present in it.
Step 1: Remove white spaces at the beginning or the end of the string
TRIM(): This function takes a cell with content and as a result, returns the same text without any white space at the beginning or end.
Input: " Hello, How are you?"
Output: Hello, How are you?

Output

Step 2: Count the number of characters with white spaces between the words
LEN(): This function calculates the number of characters present in the cell.

Output

Step 3: Replace the spaces with no space and then calculate the number of characters
SUBSTITUTE(): This function replaces a piece of text with another bit of text.
Syntax:
SUBSTITUTE ( cell name, old_text, new_text)

Output

We can see that the spaces are removed.
To count the characters, we can add the LEN function with the substitute function line LEN(SUBSTITUTE(cell_name)).

Output

Step 4: Calculating the Difference

Output

Step 5: Add 1 to the difference value obtained

Output

The count of words in "Hello!, Welcome to GeeksforGeeks" is 4. Therefore, the formula to calculate the count of words in a cell is:
= LEN(TRIM(cell_name)) - LEN(SUBSTITUTE(A2, " ", "") + 1
How to Count the Total Number of Words in a Range
The formula that calculates the total number of words in a cell can be embedded within the SUM function to determine how many words are present in a specific range.
Syntax:
=SUM(LEN(TRIM(cell_range))-LEN(SUBSTITUTE(cell_range," ",""))+1)
Example:
Suppose we have text in three cells, and we want to calculate the total words present in those cells.

Formula
Use the Formula below:
=SUM(LEN(TRIM(A2:A4))-LEN(SUBSTITUTE(A2:A4," ",""))+1)

Output

How to Count Specific Words in a Range
If you want to count the recurrence of a particular word or text within a range of cells, use a similar approach - take the formula to count specific words in a cell, and combine it with the SUM.
=SUM((LEN(range)-LEN(SUBSTITUTE(range, word,"")))/LEN(word))
As the above formula is an array SUM formula, Press Ctrl+Shift+Enter to correctly implement it.
Example
To count all occurrences of the word entered in cell C1 within the range A2:A3, use this formula:
=SUM((LEN(A2:A3)-LEN(SUBSTITUTE(A2:A3, C1,"")))/LEN(C1))

As, SUBSTITUTE is a case-sensitive function, and therefore the above formula distinguishes between uppercase and lowercase text:

To make the formula case-insensitive, use either the UPPER or LOWER function:
=SUM((LEN(A2:A3)-LEN(SUBSTITUTE((UPPER(A2:A3)),UPPER(B2),"")))/LEN(B2))
Or
=SUM((LEN(A2:A3)-LEN(SUBSTITUTE((LOWER(A2:A3)),LOWER(B2),"")))/LEN(B2))

In this article, I've shown you how to count words in Excel. Hopefully, this article will prove to be of help to you, I advise you to read more articles on Excel if you want to understand more. I appreciate your time and look forward to hearing from you soon!
Similar Reads
How to Sum a Column in Excel: 3 Methods
Knowing how to sum a column in Excel is essential for managing budgets, financial reports, and large datasets. Whether youâre calculating sales totals, adding expenses, or analyzing business data, Excel provides multiple ways to add up a column efficiently. Many users wonder "how to add columns in E
5 min read
How to Configure Macros in Excel
An action or collection of actions can be recorded, named, stored, and executed as many times as necessary and whenever desired using an Excel macro. We can automate repetitive data manipulation and reporting processes that need to be done repeatedly by using macros. You can record a macro and save
4 min read
How to Add a Comment in a VBA in Excel?
VBA Macro is for developers. Macro is a piece of code written in VBA. VBA is Microsoft's programming language and it stands for Visual Basic for Applications. Let's see how to set up our VBA Macro and how to add comments in a VBA in Excel. VBA Macro Excel is a very advanced tool that contains thousa
4 min read
Excel VBA | count() functions
Visual Basic for Applications (VBA) is the programming language of Excel and other offices. It is an event-driven programming language from Microsoft. With Excel VBA one can automate many tasks in excel and all other office software. It helps in generating reports, preparing various charts, graphs a
2 min read
How to Count Unique and Distinct Values in Excel
Counting unique values in Excel is a common challenge when working with large datasets, especially for those analyzing data or generating reports. Knowing how to count distinct Excel values accurately is crucial when managing data like customer lists, product inventories, or sales records. This arti
15 min read
How Can I Enable Python in Excel?
Excel and Python are a dynamic combination that may help anyone improve their automation and data analysis skills. Not only can Python help you become more proficient with spreadsheets, but it can also revolutionize efficiency and insight. This is how to easily enable Python in Excel to open up a pl
3 min read
How to count words in real time using jQuery ?
In this article, we will learn how to count words in real-time using jQuery. We have an input field, and when we type, we get the number of words in the input in real time. Approach: First, we select the input field and store its value in a variable. We call a function called wordCount() when a keyb
2 min read
How to Enable & Run a Macro in Excel? 6 Ways
We can record a macro in Excel or write from scratch in VB Editor. Excel provides different options to run a macro. In this article, we explore four different approaches to running a macro in an Excel application. Even while executing an Excel macro is a straightforward process for seasoned users, i
5 min read
How to Create an Expense Tracker in Excel?
Finance management plays an important role in an organization or in the personal finance of people's life. In order to manage finances organizations have a finance manager, in the same way individual person can manage their own expenses using an expense manager. Expense Manager, also known as Expens
10 min read
How to Extract the Last Word From a Cell in Excel
Explore how to extract the last word from a cell in Excel. Whether youâre working with text data, names, or any other content, knowing this technique can be incredibly useful. In this article, we explain how to extract the last word from a text in a cell using the Excel function. Â Extracting words f
3 min read