Buzz
There isn't a single formula for counting words; however, the LEN function can be used to count characters including spaces between words. Instead of counting words directly, we count the spaces in the word string and add one. This can be achieved by combining the LEN function with the SUBSTITUTE function and the TRIM function.
Statistical Functions in Excel
Counting Words in Excel, within Cells, Rows, Columns
First, let's explore the functions of each formula.
- LEN Function: Counts the number of characters in a string, including spaces within the cell.
Syntax: LEN(cell)
Example: Count the number of characters in cell B5
- SUBSTITUTE Function: Replace old characters with new ones
Syntax: SUBSTITUTE(text or cell, old character, new character)
Example: Replace spaces in cell B6 with empty character
- TRIM Function: Removes extra spaces within a cell. For example, leading and trailing spaces or extra spaces between words.
Syntax: TRIM(cell)
Example: Remove leading spaces and extra space between the words 'free' and 'of charge' in cell B7
Counting Words within a Cell
We combine these three functions to count the number of spaces using the formula: =LEN(TRIM(B5))-LEN(SUBSTITUTE(B5,' ','')) + 1. The final result counts as shown below.
The formula above can be analyzed as follows:
- Len(Trim(B5)): used to count the number of characters in cell B5 including spaces. Trim function helps remove extra spaces in this cell for more accurate character counting.
- LEN(SUBSTITUTE(B5,' ','')): Counts the number of characters in cell B5. The SUBSTITUTE function ensures that Len doesn't count spaces.
- When subtracting the two combined functions, we get the number of spaces in the cell, we then add one to get the number of words in the cell.
We apply this formula to cell B7, which has leading spaces and contains 2 words 'Free' and 'Of Charge'. The result remains the same.
Counting Words within a Data Range
From counting words within a cell as described above, we can count the number of words within a data range using sum functions like SUMPRODUCT or SUM. Essentially, we add up the word counts of each cell within the range to get the total number of words.
=SUMPRODUCT(LEN (TRIM (data range)) - LEN (SUBSTITUTE (data range, ' ', '')) + 1)
great
= SUM (LEN (TRIM (data range)) - LEN (SUBSTITUTE (data range, ' ', '')) + 1)
Example: Let's count the number of words from cell B5 to B7.
- SUMPRODUCT Function.
- The SUM Function: When using the SUM function, you need to press Ctrl + Shift + Enter instead of just Enter to perform array calculations.
Here's a guide on how to count words within a cell and within a data range in Excel. We hope that after reading this article, you can easily apply these functions to count words in Excel. If you encounter any difficulties during the process, don't hesitate to comment below, and Mytour's technical team will assist you. Remember to use the count function for easy use in Excel.
Moreover, when working with Word, you can also easily count words in Word. For detailed instructions on how to do this, refer to here, how to count words in Word 2016, 2013, 2010.
If you're learning about the DATEDIF function, check out the previous article on the DATEDIF function to understand more, and use the DATEDIF function effectively.
