![]() =(LEN( cell)-LEN(SUBSTITUTE( cell, word,"")))/LEN( word)įor example, let's calculate the number of " moon" occurrences in cell A2: To count how many times a certain word, text, or substring appears in a cell, use the following formula: =IF(A2="", 0, LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1)Īs you can see in the screenshot above, the formula returns zero for blank cells, and the correct word count for non-empty cells. To fix this, you can add an IF statement to check for blank cells: The above formula to count words in Excel could be called perfect if not for one drawback - it returns 1 for empty cells. Improved formula that properly handles empty cells To guard against this, before calculating the total length of the string, we use the TRIM function to remove all excess spaces except for single spaces between words. And all those extra spaces can throw your word count off. Sometimes a worksheet may contain a lot of invisible spaces, for example two or more spaces between words, or space characters accidentally typed at the beginning or end of the text (i.e. =LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1Īnd then, you can copy the formula down to count words in other cells of column A:įirst, you use the SUBSTITUTE function to remove all spaces in the cell by replacing them with an empty text string ("") for the LEN function to return the length of the string without spaces:Īfter that, you subtract the string length without spaces from the total length of the string, and add 1 to the final word count, since the number of words in a cell equals to the number of spaces plus 1.Īdditionally, you use the TRIM function to eliminate extra spaces in the cell, if any. ![]() Where cell is the address of the cell where you want to count words.įor example, to count words in cell A2, use this formula: ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |