WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Word Count in Excel

Word Count in Excel

How to Count Total Number of Words in Excel Cell?

Below are the easy and useful steps to count words in excel cell.

  • Step 1 – Select the data in your excel sheet on which you want to count the words in a particular cell.

Word Count Example 1

  • Step 2 – Now we need to check the number of words is in the present “Cell A2”. Enter the formula in the cell and select the cell.

Word Count Example 1-1

  • Step 3 – Click Enter, and you will get the exact number of words in a particular cell.

Word Count Example 1-2

How to Count the Total Number of Words in a Particular Cell?

To discover what number of words a specific range contains, take the equation that counts all out words in a cell and implant it inside either SUMPRODUCT or SUM work:

Formula to count words in a particular range is = LEN(TRIM(cell))-LEN(SUBSTITUTE(cell,” “,””))+1

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course
  • Step 1 – Select the range in data to count the exact number of the word in that cell or range.

Word Count Example 2

  • Step 2 – Enter the formula in a cell where you want to get the result:

Word Count Example 2-1

  • Step 3 – Press Enter, you will get the result of the word count of a particular cell.

Example 2-2

  • Step 4 – Drag the formula in all the cells to get the Excel word count in each of the cells:

Example 2-3

How to Count the Specific Words in a Range?

If you need to count how often a specific word or content shows up inside a range of cells, utilize a comparative methodology, use the formula to count explicit words in a cell, and consolidate it with the SUM or SUMPRODUCT work:

=(LEN(cell)-LEN(SUBSTITUTE(cell, word,””)))/LEN(word)

  • Step 1 – Select the cell where you want to enter the formula to count excel words from the particular range:

Example 4

  • Step 2 – Click enter to check the Excel word count in cell A2. We are using this to get the result of how many

Example 4-1

time the word February is present in a particular cell.

How Does this Word Count Formula Work?

To begin with, you utilize the SUBSTITUTE function to evacuate all spaces in the cell by displacing them with a vacant content string (“) for the LEN function to restore the length of the string without spaces:

Formula explanation for Excel word count: LEN(SUBSTITUTE(A2,” “,””))

From that point onward, you subtract the string length without spaces from the absolute length of the string, and add 1 to the last word count, since the number of words in a cell equivalent to the number of spaces in addition to 1.

Furthermore, you utilize the TRIM function to take out additional areas in the cell, assuming any. Once in a while, a worksheet may contain a ton of imperceptible spaces, for instance, at least two spaces between words or space characters coincidentally composed toward the start or end of the content (for example, beginning and ending spaces). And each one of those additional areas can distract your pledge count. To make preparations for this, before computing the all-out length of the string, we utilize the TRIM function to evacuate every single abundance space aside from single spaces between words.

Things to Remember

  1. If you are planning to copy your formula to several cells, make sure to fix the formula and fix the reference to the cell, which contains the word to count with the $ sign in the formula.
  2. In excel, this formula is not inbuilt, so we need to use this formula by entering manually to count the number of words.
  3. Always use the correct range in the cell while using the formula.
  4. Always check the cell for which you are using a particular formula so that you got the better result.
  5. This formula is not case sensitive, so you can use this formula in any type of alphabetical letters.

Recommended Articles

This has been a guide to Words Count in Excel. Here we discuss how to count the total number of words in a cell and a range of cells using excel formulas (LEN, SUBSTITUTE, TRIM) along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • Count Rows in Excel | Examples
  • Count Colored Cells In Excel
  • Excel Count Formula
  • How to Count Unique Values in Excel?
  • Compare Two Lists in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

WallStreetMojo

Download Words Count Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More