Count instances in a cell

[laxjuan05]

ID	Submitted	# submitted
1.1	1/1/18 2/5/18	2
1.2	6/5/18	1
1.3	2/1/18 2/5/18 3/1/18	3

<tbody>
</tbody>

Hello!

Is there a way to count instances in a cell. have a cell that contains multiple submittal dates and want to know if there is a way to get the number of entries in a cell? Is there a way to get column in the example chart above.

Thank you in advance!

 

[BenMiller]

With the "Submitted" column being Column B, try this:

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

 

[laxjuan05]

Ben,

Thank you so much for your help! This worked great!

 

[laxjuan05]

Ben,

Was looking at the data I have and I have an issue with the formula and my data. Example is not shown in the example data, but there are ID's (Column A) where # submitted (Column B) is blank. Currently the formula returns value of 1 if cell column B is blank. Is there away to return nothing or a 0 if cell is blank?

 

[BenMiller]

Try

=IF(ISBLANK(B2),"",LEN(B2)-LEN(SUBSTITUTE(B2,CHAR(10),""))+1)

 

[laxjuan05]

Thanks Ben! Worked perfect!

 

[BenMiller]

Thanks Ben! Worked perfect!