To count the number of words in a cell in Excel, you can use the LEN function together with the SUBSTITUTE function. The SUBSTITUTE function is used to replace all spaces in the cell with nothing (i.e. remove all spaces), and the LEN function is used to count the number of characters in the resulting string. Here is the formula:
=LEN(SUBSTITUTE(A1, ” “, “”))
This formula will count the number of words in cell A1. Note that this formula counts words by counting the number of characters in the cell and assuming that each group of characters separated by a space is a word. This means that this formula will not work correctly if the cell contains punctuation or numbers.
To count the number of words in a cell that may contain punctuation or numbers, you can use the LEN and SUBSTITUTE functions together with the FIND function. The FIND function is used to locate the position of the first space in the cell, and the LEN and SUBSTITUTE functions are used to count the number of characters in the cell up to that space. Here is the formula:
=LEN(A1)-LEN(SUBSTITUTE(A1, ” “, “”))+1
This formula will count the number of words in cell A1 even if the cell contains punctuation or numbers.
Here is a simple example:
Also Read: Excel Formula To Count The Number Of Items In A Cell Separated By Commas