Excel: Counting the Number of Words or Characters in a CellLast reviewed: September 2, 1997Article ID: Q81564 |
The information in this article applies to:
SUMMARYMicrosoft 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," ",""))+1The 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," ",""))+1You 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
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |