excelvbamacosoffice365

How do i count items in a multi item drop down cell


I have a Mac Excel spreadsheet which includes multi entry drop down cells. Each cell uses a drop-down list that allows for multiple selections separated by by a line break with no delimiter. How do I count the number of selections in each cell? or alternatively, how do i include a delimiter with each multi entry selection while retaining the line break?

I can count the entries in a cell where there is a delimiter:

=LEN($E7)-LEN(SUBSTITUTE($E7,",",""))

But not without the delimiter


Solution

  • If there is line break then you can use that as delimiter. Use Char(10) as delimiter.

     =LEN($E7)-LEN(SUBSTITUTE($E7,CHAR(10),""))+1
    

    enter image description here