Count instances in a cell

[laxjuan05]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID

[/TD]
[TD]Submitted
[/TD]
[TD]# submitted
[/TD]
[/TR]
[TR]
[TD]1.1
[/TD]
[TD]1/1/18
2/5/18
[/TD]
[TD]2

[/TD]
[/TR]
[TR]
[TD]1.2
[/TD]
[TD]6/5/18
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]1.3
[/TD]
[TD]2/1/18
2/5/18
3/1/18
[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]
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!