count number of words

finnstone
finnstone used Ask the Experts™
on
i need to count the number of words in each cell that are comma separated

example

analysis, engagements, initiatives, scale
Awards, exclusive, national, retail, stores stores, Voted
convergence, enterprises, partnership, partnership
edge, Profit
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Analyst Assistant
Commented:
If the words were in column A starting in row 1 try this formula in B1 and copy it down.

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1
Rob HensonFinance Analyst

Commented:
In case the last word is followed by a comma:

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial