How to Get Word Count in Excel?
To count a number of words in a given cell we have some useful inbuilt functions in excel such as the =LEN() function, this function calculates the length or the number of characters present in a given text in the cell, we can improvise by using other excel functions such as If function to count the number of words if there are other characters present in the cell apart from the words such as spaces.
- LEN() = restores the length of a string (i.e., discloses to all of you the characters incorporating spaces in a phone)
- TRIM() = expels all spaces from a string of content with the exception of spaces between words
- SUBSTITUTE() = replaces all occurrences of something in a cell (for this situation replaces ” all spaces with no space””)
With the combination of these three functions, we will be able to find the word count in excel, in a particular cell or in any other complex situation.
How to Count Total Number of Words in Excel?
Step 1 – Select the data in your excel sheet on which you want to count the words in a particular cell.
Step 2 – Now we need to check the number of words is in present “Cell A2”. Enter the formula in the cell and select the cell.
Step 3 – Click Enter and you will get the exact number of words in a particular cell.
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:
4.9 (1,353 ratings)
Formula to count words in a particular range is = LEN(TRIM(cell))-LEN(SUBSTITUTE(cell,” “,””))+1
Step 1 – Select the range in data to count the exact number of word in that cell or range.
Step 2 – Enter the formula in a cell where you want to get the result:
Step 3 – Press Enter, you will get the result of the word count of a particular cell.
Step 4 – Drag the formula in all the cells to get the Excel word count in each of the cells:
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:
Step 2 – Click enter to check the Excel word count in cell A2. We are using this to get the result of how many
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 (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
- 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.
- In excel this formula is not inbuilt, so we need to use this formula by entering manually to count the number of words.
- Always use the correct range in the cell while using the formula.
- Always check the cell for which you are using a particular formula so that you got the better result.
- 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 downloadable excel template. You may learn more about excel from the following articles –
- Consolidate in Excel
- Excel Text Formula
- VBA TRIM Function
- Grade Formula in Excel
- How to Uppercase in Excel?
- Add Text Formula in Excel
- Find and Replace in Excel
- How to Remove Space in Excel?
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
