Need to count occurrences of a text + increasing number on the same cell

[Valco]

Hi,

I am trying to calculate the number of times the same character appears with a number on the same cell.

For eg: If Cell A1 contains the following string of character: Y1, Y24, Y19, Y23, Y1, Y17
I need to count the number of times Y1 appears (and Y17 & Y19).

Using the =LEN(A1)-LEN(SUBSTITUTE(A1,"Y1","")) counts this as 4 occurrences (includes Y17 & Y19).
Is there a way we can check if there is another number following Y1 before it counts it as 1?

PS: I am new on this forum and apologize beforehand if this question has already been answered.
Your help in this matter is greatly appreciated.

[Glenn Kennedy]

If the commas are there you could try
=LEN(A1)-LEN(SUBSTITUTE(A1,"Y1,",""))

[Glenn Kennedy]

...assuming that it's the number of times that Y1 appears, that you're trying to count

[Valco]

Thanks Glenn for your quick response.

It worked like a charm after making some adjustments

Thanks again for your help.

[Glenn Kennedy]

Thanks for the Reputation that you have added. It's much appreciated. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.