Len and substitute to return 0 if cell is blank?

Question

I'm using BYROW to pull a string of dates from one sheet and make a list of those dates in a single cell on another sheet. Now I'm trying to use LEN and SUBSTITUTE to count the number of dates in that cell:

=LEN(M13)-LEN(SUBSTITUTE(M13,",",""))+1

I have to add the +1 at the end of the formula to count the last date, but some cells have no dates, and I need those to pull a 0 if they stay blank. Does this make sense? So if I add a comma after the last date, it throws off my BYROW function that gets me the dates in the first place.

https://docs.google.com/spreadsheets/d/1MRkacDUNoa0QXMYG5njBp7E7NepRARKXb4IEHjWnxsw/edit?usp=sharing

Answer 1

You can simply nest the LEN inside an IF to first check the cell if it's empty, in which case it will output empty (or 0 if you want.)

Something like:

=IF(M2<>"",LEN(M2)-LEN(SUBSTITUTE(M2,",",""))+1,"")

• IF M2 is not empty, output the result of the LEN
• IF M2 is empty, output empty (change the last "" to 0 if you want a 0 instead)