Count the number of words in a cell separated with with two different signs

Hi guys,

Can someone help me with a formula that counts the number of words in cells like these:
CELL1: Aichele & Rauner
CELL2: Andrus, Sceales, Starke & Sawall
CELL3: Antonelli, Terry & Wands

The solutions for which would be 2,4 and 3.

The problem is that the words are separated with both a comma, and the "&" sign.

thx!

Try this:

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

The commas are not really a problem unless you have a space in front of them.

Hope this helps.

Pete

Works like a charm! Thank you for the fast response.