Need Help With a Formula for Google Sheets

Topic

New Reply

Hi all,

Can someone help me figure out a formula for Google Sheets?

See the attached screenshot. Simply put, I want Column D to be calculated automatically, based on the number of dates in Column E.

The thing is, I use a specific syntax for Column E: Each new item begins on a new line, preceded by a triangle bullet point (►).

Also, if Column E is empty, then Column D should report “0.”

Can someone help me with the formula I need for Row D?

Thanks so much!

Reply | Quote

Replies

Your picture only shows one date in E. How do you enter more than one date, do you ever have more than one date?

Can you count the number of slashes, or is that a formatting artefact?
A regex search may work. Count the number of matches for 4 digits, or however many you use in your cell.

cheers, Paul

1 user thanked author for this post.

WSJon5

Reply | Quote

Thanks so much for your reply, Paul. And my apologies for sending an incomplete screenshot; here’s a better one. Does this help?

Counting the number of “►” would, I think, do the trick.

Thanks again!

 

Reply | Quote

This formula will give you the result you need, but it requires you use the arrow symbol in the split (can you do that?). I’ve used “K” as the first character instead of the arrow symbol.

=count(SPLIT(B1, "K"))

cheers, Paul

1 user thanked author for this post.

WSJon5

Reply | Quote

Thanks so much, Paul! This did the trick! Much obliged.

Reply | Quote

Here is a really ugly formula to remove all the single and non digits, split the result and then count. Allows you to use any character as the symbol.

=COUNT(SPLIT(REGEXREPLACE(B1,"(\D)(\D\d)/"," ")," "))

cheers, Paul

1 user thanked author for this post.

WSJon5

Reply | Quote