count terms in a cell

[zvika_1]

any idea how to count how many terms are in a cell ?

 

[Yogi Anand]

Hi Zvika_1:

Do you mean how many items are in a range of cells? If you do mean within a cell, then do you mean how many characters are within a particular cell?

Please clarify.

Regards!

Yogi

 

[zvika_1]

Thanks Yogi
I mean how many words in 1 cell.
example: Cell A1 contain the words: Mr Excel Message Board (4 words).

 

[Yogi Anand]

Hi Zvika_1:

Use ...

=Len(A1)-Len(Substitute(A1," ",""))+1

if A1 houses Mr Excel Message Board, the result will be 4.

Regards!

Yogi

 

[Derek]

Hi there

Just a thought, if you have extraneous spaces you might like to add Trim to Yogi's formula

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1

regards
Derek

 

[zvika_1]

Thanks Yogi & Derek
Great
Zvika

 

[zvika_1]

Hi Yogi & Derek
Is it possible now to point to any ward in a cell and switch between words ?
Example: A1 houses "Mr Excel Message Board"
New order: "Mr Excel Board Message"
Regards
Zvika

 

[Andrew Poulsom]

Like this?

=SUBSTITUTE(SUBSTITUTE(A1,"Message","Board"),"Board","Message",2)

 

[zvika_1]

No.
A variable is needed
Zvika

 

[Andrew Poulsom]

What's the variable?