Count Text from Multi-Select Drop-Down List

[OVUlefty2]

I have a column for each record where a multi-select drop-down list displays various countries. I would like to count the number of selections (in this case countries) that each row has selected.

Example: Row 2 has the United States, Canada, Mexico, and South Africa selected in B2 and Row 3 has Canada, Brazil, and France selected in B3. I would like to return "4" in C2 and "3" in C3.

 

[jmacleary]

If your list is comma separated in each cell then this should work: In C2 put this formula: =LEN(TRIM(B2))-LEN(SUBSTITUTE(TRIM(B2),",",""))+1 and drag down. (I got this trick from another forum somewhere ages ago - I forget which though!).