How to find number of words in a cell (sentence) using excel formula?

see link below

http://www.extendoffice.com/documents/excel/933-excel-count-words.html

=LEN(A1)

If you have the following sentence in cell A1 of a spreadsheet:"How are you"We can calculate the number of words by using a combination of three different Excel functions: IF, LEN and SUBSTITUTE.The LEN function will allow us to count the number of characters in a particular cell (this includes spaces between words). In this example =LEN(A1) will return a value of 11 characters.SUBSTITUTE allows us to replace a common string of text, or character(s) in a text string with another set of predefined character(s). This will allow us to remove all spaces from the sentence using the following command: =SUBSTITUTE(A1," ",""). This will return the following text string: "Howareyou".If we combine the before mentioned funtions in the following formula: =LEN(SUBSTITUTE(A1," ","")) we will return with a value of9 (spaces are now ommited).

If we now take the LEN command in conjunction with our last formula, we are able to calculate the spaces in the sentence and thus determine where words in the sentence are being seperated. The formula we will use is: =LEN(A1)-LEN(SUBSTITUTE(A1," ","")).

This will calculate that our sentence contains two spaces. We do however know that a word must logically follow a space in a sentence to be gramatically correct. So we need to force the system to add the last word, preceding the last space, in the word count automatically or to return a0 value if no words are entered in the referenced cell. We can achieve this by using an IF statements' logical_test, if_true- and if_false values.

The formula will thus be:

=IF(LEN(A1)=0,0,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)

Value returned = 3

=len(A1) or len("Value")

use formula of count a

Yes i agree, its using LEN function under Sub-tab "Text" in the Formulas Tab, you can count the characters in the Cell including spaces.

the correct answer is  the  =LEN(SUBSTITUTE(A1," ",""))+1

which is near to the answer of mr  johandre

Check This Link

http://office.microsoft.com/en-001/excel-help/count-the-number-of-words-in-a-cell-or-range-HA.aspx

To find number of word in a cell A1 contains "How are you".

=LEN(A1)...the result. 11

agree with Nitin Gupta & Johandre buys

=IF(LEN(A1)=0,0,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)

By the LEN function you can find the number of words in a cell or sentence