Excel: Counting the Number of Words or Characters in a Cell

 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 "The car drove fast," 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," ",""))+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," ",""))+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.

REFERENCES

"Function Reference," version 4.0, pages 422-423 "Function Reference," version 3.0, page 231

Additional query words: 2.00 2.01 2.1 2.10 2.20 3.00 4.00
Keywords : PgmOthr

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 2, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.