CODEWITHSUNDEEP
vbaexcelexcel-formula
guybrianadams
guybrianadams

Reputation: 33

Count number of values in a cell unless blank

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?

enter image description here

Upvotes: 1

Views: 435

Answers (1)

user4039065
user4039065

Reputation:

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))

Upvotes: 3

Related Questions