Hm, that's an interesting question. If you're looking for just the n of terms in the array, you can do this without splitting the terms.
=SUM(ARRAYFORMULA(LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4, ",", "")) + 1))
Alright, here's what's happening in this formula.
- In A1, we take the
len() of the string and subtract the
len() of the string after substituting the comma. In this case we get
9 - 9 = 0. We then add
1 to get
1 (the number of terms in the cell)
- We can use
arrayformula() on this to apply the same subtraction function to every cell in the given range (in this case
- The final piece is to
sum() the numbers from the resulting range and get the total # of terms that are split on the comma.
Let's look at cell A2 to see a case of when the comma is used. We take
len(A2) which is 10 and subtract
len(A2) after substituting the comma for a null character "" which is 9. We get
10 - 9 = 1 then we add
1 to the total to get
2 which is equal to the number of terms in the cell.
Hope this helps!