How to count the number of words in a cell with line breaks?

Hi,

I am making a content database and need to count the number of words in each cell...

I know you can count them with

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,” “,””))+1)

but the the cells have line breaks so this formula won't work

I've understood that since there is a space before the new line, the formula will not recognise the space and therefore not recognise a new word.

Any ideas on how I can work this out?

i want to collect a particular cell content of different worksheets of a same workbook. can any one help me?

I tried both Olly's and XOR LX's functions but I get the same as number of words as I did with the my original function.

I've noticed that for every line break, there's a word that isn't counted.

An example of cell would be:

Question: How old are you?
1.How old are you?*
2. How are you?

All functions proposed so give me 13, but there are 15.

The numbers should be included in the word count, sorry for not clarifying that.

But for another example:

"Listen to these two sentences:
How old is he?
He’s forty."

I get 9 words when it should be 11.
I know it's because of the line breaks. I've used ALT RETURN to create new lines...

I opened a new sheet but still the same problem. Any suggestions? I'm a bit of newbie at excel