计算单元格中的值数，除非为空

本文介绍了计算单元格中的值数，除非为空的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个使用下拉列表的单元格，该列表允许进行多项选择.如何计算选择的数量?

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

我用了这个: = LEN(A2)-LEN(SUBSTITUTE(A2，，"，"))+ 1

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

但是，这并不代表空白或没有选择.如果没有选择，如何显示0?

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

推荐答案

如果选择的内容中没有空格，则用逗号替换空格，修剪结果，然后用逗号将空格计为上述值.

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