Function/formula to count text string with wildcard in single cell

[Tigerexcel]

Does anyone know a way of counting the number of occurrences of a string with a wildcard within a single cell using a function, array or formula?

MEB Student Performance (2).xlsx
	A
1	ABC2020:P:57, ABC2040:P:53, ABC2050:P:58, ABC2080:N:48,
Sheet1

I would like to find the number of times ABC2*P* occurs, which in this case is 2. A combination of LEN and SUBSTITUTE seems to work if the desired text string does not contain a wildcard but I can't get anything to work when there's a wildcard involved.

 

[Peter_SSs]

.. so the example in post 18 was a valid example or not?
If valid, the correct count for ABC2*P* is ..?

 

[Tigerexcel]

.. so the example in post 18 was a valid example or not?
If valid, the correct count for ABC2*P* is ..?

Yes, 0 is the correct result.

 

[Peter_SSs]

Yes, 0 is the correct result.

Thanks for the confirmations.