Counting how many substrings in a cell
By bruce mcpherson
Sometime you need to know how many substrings there are in a cell containing a string, where each substring is seperated by, say, a comma.
Consider the string Peter,Paul,Mary - how would you count how many people are in the list?
Let's say your list is in B2.
=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1
Simply counting the string before and after the removal of commas will tell you how
many are in the list.
Counting how many substrings in a cell (717 Views)