Microsoft KB Archive/81564

{| = Excel: Counting the Number of Words or Characters in a Cell =
 * width="100%"|

Last reviewed: September 2, 1997

Article ID: Q81564

The information in this article applies to:
 * Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0
 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0

SUMMARY
Microsoft Excel does not have a built-in function to count the number of words in a cell. However, the following example demonstrates a method to count the number of words in a cell that are separated by a specific character.

If cell A1 contains the text string &quot;The car drove fast,&quot; the following formula will return a value of 4 to reflect that the string contains four words separated by spaces:

=LEN(A1)-LEN(SUBSTITUTE(A1,&quot; &quot;,&quot;&quot;))+1 The space between the first two double quotation marks represents the character separating each word to be counted. If the items to be counted are separated by commas, place a comma between the first two quotation marks. As a word counter, this will return an incorrect number if words are separated by multiple spaces (for example, a double space following a period). Based on the assumption that every word (and only a word) except the last is separated by a space, the following formula will always return an accurate number:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,&quot; &quot;,&quot;&quot;))+1 You can also use this formula to count the total occurrences of a specific character by removing the +1 at the end of the formula. To count the total occurrences of a specific character, that character must be entered as the second argument in the SUBSTITUTE function.