Count Words in a Cell of Excel
There is no in-built excel formula to find the Word Count and therefore, it needs to be entered manually. You can make use of the formula below to calculate wordcount in excel –
Let us understand the working of this formula.
To begin with, we utilize the SUBSTITUTE functionSUBSTITUTE FunctionSubstitute function in excel is a very useful function which is used to replace or substitute a given text with another text in a given cell, this function is widely used when we send massive emails or messages in a bulk, instead of creating separate text for every user we use substitute function to replace the information. to evacuate and displace all spaces in the cell with a vacant content string (“). The LEN functionLEN FunctionThe Len function returns the length of a given string. It calculates the number of characters in a given string as input. It is a text function in Excel as well as an inbuilt function that can be accessed by typing =LEN( and entering a string as input. restores the length of the string without spaces.
Next, we subtract the string length without spaces from the absolute length of the string. The number of words in a cell is equivalent to the number of spaces plus 1. So, we add 1 to the word count.
Further, we utilize the TRIM functionTRIM FunctionThe Trim function in Excel does exactly what its name implies: it trims some part of any string. The function of this formula is to remove any space in a given string. It does not remove a single space between two words, but it does remove any other unwanted spaces. to remove extra spaces in a cell. A worksheet may contain a lot of imperceptible spaces. Such coincidental occurrence might be towards the start or end of the text (leading and trailing spaces). Since extra spaces return an incorrect word count, the TRIM function is used before computing the length of the string.
The steps to count the total number of words in a cell of Excel are listed as follows:
- Select the cell in the Excel sheet where you want the result to appear.
- For counting the number of words in cell A1, enter the formula shown in the following image.
- Click “Enter” and the exact number of words appear in cell B1.
Table of contents
- The formula to count words of a particular range is “=LEN(TRIM(cell))-LEN(SUBSTITUTE(cell,” “,””))+1.”
- The word count formula is combined with the SUM or SUMPRODUCT function to handle arrays.
- The SUBSTITUTE function replaces all the spaces of the cell with a vacant content string (“).
- The LEN function restores the length of the string without spaces.
- The TRIM function removes the leading and trailing spaces found at the beginning or at the end of the text.
- The number of words in a cell is equivalent to the number of spaces plus 1.
How to Count the Total Number of Words in a Range of Cells?
To count the number of words in a range of cells, apply the equation that counts the words in a cell and implant it either inside the SUM or the SUMPRODUCT function.
The formula to count words of a particular range is “=LEN(TRIM(cell))-LEN(SUBSTITUTE(cell,” “,””))+1.”
- Step 1: Select the range of data whose words you wish to count.
- Step 2: Enter the formula in the cell where you want the result to display as shown in the succeeding image.
- Step 3: Click “Enter” and the result appears in cell B1.
- Step 4: Drag the fill handle to all cells to get the word count of each cell in Excel.
How to Count Specific Words in a Range?
To count the number of times a specific word appears in a range of cells, we utilize a comparative methodology. We count the explicit words in a cell and consolidate it with the SUM or SUMPRODUCT functionSUMPRODUCT FunctionThe SUMPRODUCT excel function multiplies the numbers of two or more arrays and sums up the resulting products..
- Step 1: Select the cell and enter the formula “=(LEN(cell)-LEN(SUBSTITUTE(cell,word,””)))/LEN(word)” as shown in the following image.
- Step 2: Click “Enter” to see the word count in cell A14. The formula we used above helps us know the number of times the word “February” is present in cell A2.
The result in cell A14 is 4.
Frequently Asked Questions
The formula to count the occurrence of a single character in a cell is stated as follows:
The “cell_ref” stands for cell reference. The letter “a” stands for the character that the user wants to count.
The formula to count the occurrence of a single character in a range of cells is stated as follows:
The “range” stands for the range of cells to which the formula is applied. The letter “a” stands for the character that the user wants to count.
The steps to count the number of times a particular word appears in a row or a column are listed as follows:
1- Select the row or the column in which the word is to be counted. The row is selected by clicking the number on the left-hand side. The column is selected by clicking the letter that appears on top of the column.
2 – In the formula tab, click “Define Name” and enter a name for the row or the column in the “New Name” box.
3 – If the column is named “NamesColumn,” the cells in this column will use “NamesColumn” for reference.
4 – Apply the formula “=COUNTIF(NamesColumn,”Jack”)” to count the number of times “Jack” appears in the “NamesColumn.”
Note: Every time a new name is added to a cell of “NamesColumn,” the result of the formula will automatically update.
This has been a guide to Word Count in Excel. Here we discuss how to count the total number of words in a cell and an excel range along with practical examples and a downloadable template. You may learn more about Excel from the following articles –
- Count Rows in Excel | ExamplesCount Rows In Excel | ExamplesThere are numerous ways to count rows in Excel using the appropriate formula, whether they are data rows, empty rows, or rows containing numerical/text values. Depending on the circumstance, you can use the COUNTA, COUNT, COUNTBLANK, or COUNTIF functions.
- Count Colored Cells In ExcelCount Colored Cells In ExcelTo count coloured cells in excel, there is no inbuilt function in excel, but there are three different methods to do this task: By using Auto Filter Option, By using VBA Code, By using FIND Method.
- Excel Count FormulaExcel Count FormulaThe COUNT function in excel counts the number of cells containing numerical values within the given range. It is a statistical function and returns an integer value. The syntax of the COUNT formula is “=COUNT(value 1, [value 2],…)”
- How to Count Unique Values in Excel?How To Count Unique Values In Excel?In Excel, there are two ways to count values: 1) using the Sum and Countif function, and 2) using the SUMPRODUCT and Countif function.
- Compare Two Lists in ExcelCompare Two Lists In ExcelThe five different methods used to compare two lists of a column in excel are - Compare Two Lists Using Equal Sign Operator, Match Data by Using Row Difference Technique, Match Row Difference by Using IF Condition, Match Data Even If There is a Row Difference, Partial Matching Technique.