EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tips Excel Word Count
 

Excel Word Count

Madhuri Thakur
Article byMadhuri Thakur

Updated August 21, 2023

Count Words in Excel

 

 

Excel Word Count (Table of Contents)
  • Introduction to Count Words in Excel
  • How to Count Words in Excel by using formulas?

Introduction to Count Words in Excel

In this topic will learn how to Count Words in Excel, the specific or total number of words in a cell or a cell range in Excel by using formulas.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Unlike MS Office, where we can easily count the number of words, MS Excel doesn’t have a built-in function. However, we can count words in Excel by combining various functions to make a formula.

We will use Excel functions like TRIM, LEN, and SUBSTITUTE to make a formula yielding words.

Let me give you the overview of these functions one by one –

  • Trim: This function removes duplicate or extra spaces from a cell or a cell range. The spaces could even be found in a cell before or after the word.
  • Len: This function returns the length of a string in a cell or a cell range. It includes spaces as well.
  • Substitute: This function replaces anything in a cell with any text, number, etc., of our choice. In our case, this function will replace spaces with blanks.

We will now look into different scenarios of word count with examples in Excel. We will also learn how to write syntax by combining the above three functions to get the word count in Excel.

How to Count Words in Excel by using formulas?

Counting words in Excel are very simple and easy. Let’s understand the working of counting words in Excel using formulas with its examples.

You can download this Count Word Excel Template here – Count Word Excel Template

Example #1 – Count the Total words in a cell

To get the count of words in a cell, we will use the below syntax:

=LEN(TRIM(cell))-LEN(SUBSTITUTE(cell,” “,””))+1

There is a text string in cell A2, and we want to count the total number of words. So it will be shown in cell B2.

(Count Words in Excel) Example 1-1

We will first use the function Trim as below:

(Count Words in Excel) Example 1-2

Press Enter.

(Count Words in Excel) Example 1-3

Now, we get the text without any extra spaces. Sometimes a worksheet could have a duplicate or more than one space between words or spaces at the start or end of the text. The trim function removes all those extra spaces. Using this function as a caution to get the correct result is good.

We will calculate the length of the string now, as shown below:

(Count Words in Excel) Example 1-4

Press Enter to get the length of the string without any space.

(Count Words in Excel) Example 1-5

Now, we will use a SUBSTITUTE function to replace the old text with the new one in the string. Here we will replace the spaces ” with blanks. ”

(Count Words in Excel) Example 1-6

Press Enter, and now we see the string without any spaces.

(Count Words in Excel) Example 1-7

Now to get the length of the string without spaces, we will use the LEN function as shown below:

(Count Words in Excel) Example 1-8

Press Enter to get the length of the string without spaces.

(Count Words in Excel) Example 1-9

Finally, to get the count of words in the string, we will subtract the length of the string with spaces (40) from the length of the string without spaces (35) and by adding 1 to it. Adding 1 will give us the total number of words, as spaces are one less than words.

(Count Words in Excel) Example 1-10

Press Enter, and you will see the exact count of words in the string in cell A2.

(Count Words in Excel) Example 1-11

This is how we will use the formula to count the total number of words in Excel. In case we have multiple strings for word count in Excel, as shown below.

Count Word Example 1-12

We just need to drag cell B2 downwards, and the formula will be applied for all the strings.

Count Word Example 1-13

If any of the above cells in the range A2:A5 has no text or is blank, the formula would return the word count as 1 instead of 0 for that specific cell. This scenario is shown below:

Count Word Example 1-14

We can modify our formula by adding the IF function to avoid such incorrect results, making it conditional. For example, we will use IF to give a condition to the cell that if it is blank, it should return the word count as 0.

The modified formula will be like this:

=IF(cell = ““,0,LEN(TRIM(cell))-LEN(SUBSTITUTE(cell,” “,””))+1)

The above data, it will be applied in the below manner.

Count Word Example 1-15

Drag cell B2 downwards; the formula will be applied for all the strings.

Count Word Example 1-16

Example #2 – Count the Total words in a cell

We have a long sentence to count the number of words in it.

Excel Data Example 2-1

First, we will get the length of cell A2, which has no extra space. Then we will substitute the spaces in the sentence with the blanks and get its length. Finally, we will subtract the total length of the sentence from the length of the sentence without spaces. Also, we will add 1 to get the final word count. This is because, in a cell, the number of words will be several spaces +1.

Count Word Example 2-2

Press Enter to see the result.

Count Word Example 2-3

I hope you have now understood the formula and will be able to write. It is tricky, but writing this 2 or 3 times will give you more clarity.

Example #3 – Count Specific words in a cell

How can we count how many times a specific name or text has appeared in a string? We can do it by the below formula:

=(LEN(Cell)-LEN(SUBSTITUTE(CELL,word,””)))/LEN(word)

There is a text string in cell A2, and we want to count the specific words in a cell. It will be shown in cell B2.

Excel Data Example 3-1

Let’s say we want to know how often the word “home” has occurred in the above sentence. We can know this by the below formula:

Count Word Example 3-2

Press Enter to see the result.

Result of Specific words in a cell 

We will first calculate the length of the original text (A2). Now the SUBSTITUTE function will completely replace the word “home” in the sentence with blanks. With the LEN function, we will get the length of text A2 with the original “home” word. After that, we will subtract the length of this new text string from the length of the original text string. We will subtract 102-90, and that will give us 12.

And lastly, we will divide the above number by the length of the word “home”,4. This will give us the result 3. The word “home” has occurred 3 times in the above string.

Also, we can use a cell reference to the word in place, “home”.

Example #4 – Count the total number of words in a Range

In case we want to know the total number of words in a range, we can do so as below:

Excel Data Example 4-1

We will include a Sum function to calculate the total number of words. For the Sum function, to calculate the array, we will press Ctrl+Shift+Enter instead of normal Enter.

Total Number of Words in a Range

Example #5 – Count the total number of a specific word in a Range

If we want to know the total number of occurrences of a specific word in a range, we can do it with the below formula:

In this example, we want to know the total number of times the word “London” occurred in the below range A2:A4. 

Excel Data Example 5-1

We can know this with the below formula:

Total Number of Specific Word in a Range

Once we have written the formula, we will press CTRL+SHIFT+Enter to get the result, as the Sum function will calculate the array. Once we press the above keys, we will get the below result.

Count Word Example 5-3

In cell range A2:A4, the word “London” has occurred 3 times. Also, you can use a cell reference in place of the word “London”.

Things to Remember About Count Word in Excel

  • As a practice, use the TRIM function in writing the formula, as it will eliminate the chances of error if the cell or range has extra spaces.
  • Whenever you want to know the count of a specific text in a cell or range, always write that word in double quotes in the formula like “word”.
  • SUBSTITUTE is case sensitive, so be careful when writing the word you want to substitute. It should be in the same case as the original text, or it won’t be substituted.
  • Always press Ctrl+Shift+Enter to calculate the array SUM formula.

Recommended Articles

This has been a guide to Count Word in Excel. Here we discuss how to count words in Excel using formulas, examples, and a downloadable Excel template. You may also look at these suggested articles –

  1. Count Unique Values in Excel
  2. COUNTIF Excel Function
  3. Excel Count Function
  4. Count Characters in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Count Word Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Count Word Excel Template

EDUCBA

डाउनलोड Count Word Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW