Count number of values in a cell unless blank

Question

I have a cell that uses a drop-down list that allows for multiple selections. How do I count the number of selections?

I used this: =LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1

But this doesn't account for blank or no selection. How can I display 0 as well if there is no selection?

Answer 1

If your selections do not have spaces then substitute commas for spaces, trim the result then count for spaces as above with commas.

=LEN(trim(SUBSTITUTE(A2,","," ")))-LEN(SUBSTITUTE(trim(SUBSTITUTE(A2,","," "))," ",""))+sign(len(a2))

Answer 2

If you want your formula to ignore consecutive comma's:

=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,",,",","),",,",","),",",""))+1

It's doubled-up to account for odd numbers of sets of commas.

---

or to totally jack-all @Jeeped's suggestions, if the values in your drop down do not have spaces, a better way might be:

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