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.

 

[sandy666]

Does that return 0 for ABC2020:N:40, ABC1111:P:55?

why 0 if there is P ? return 1

 

[gaz_chops]

The OP stated to search for ABC2*P, that example has ABC2*N & ABC1*P, so the result would be 0.

 

[Peter_SSs]

The OP stated to search for ABC2*P, that example has ABC2*N & ABC1*P, so the result would be 0.

Yes, and although not explicitly stated, I believe the ABC2*P* is to be considered within each individual term separated by the commas

 

[gaz_chops]

@Peter_SSs can you explain your last formula please, i can follow it except I don't see how it picks up the number of ABC2*P.

 

[sandy666]

The OP stated to search for ABC2*P, that example has ABC2*N & ABC1*P, so the result would be 0.

if I will see representative example not like yours I can answer
all examples from OP aren't like yours
anyway look at post#4 where I added *P*

 

[gaz_chops]

Leave it for the OP to answer, but for the benefit of others your solution doesn't appear to work in the above example, if we are correct in that each individual term is separated by a comma.

 

[Peter_SSs]

@Peter_SSs can you explain your last formula please, i can follow it except I don't see how it picks up the number of ABC2*P.

I'll try with this example with just 3 terms and the formula reduced to allow for up to 4 terms.

20 07 23.xlsm
	A	B
1	Results	Count
2	ABC2020:D:72, ABC2040:P:52, ABC2050:P:52,	2
Sample

Cell Formulas
Range	Formula
B2	B2	=COUNT(1/(LEFT(REPLACE(TRIM(MID(SUBSTITUTE(", "&A2,", ",REPT(" ",100)),{1,2,3,4}*100,100)),5,4,""),5)="ABC2P"))

, & A2 = ", ABC2020:D:72, ABC2040:P:52, ABC2050:P:52,"

SUBSTITUTE then replaces each ", " with 100 spaces
Using period so you can see them that gives
....................................................................................................ABC2020:D:72....................................................................................................ABC2040:P:52....................................................................................................ABC2050:P:52,

Now TRIM(MID( ,{1,2,3,4}*100,100)) picks out each term & removes the spaces leaving the 4-term array
{"ABC2020:D:72";"ABC2040:P:52";"ABC2050:P:52,",""}

Now replace 4 characters of each term starting at the 5th position with nothing
{"ABC2D:72";"ABC2P:52";"ABC2P:52,",""}

Check if the left 5 characters are ABC2P
{False;True;True;False}

1/{False;True;True;False} = {Error;1;1;Error)

Count that = 2

 

[gaz_chops]

Brilliant, thanks for taking the time to explain.

It was {1,2,3,4}*100 that I couldn't follow.

Many thanks.

Gaz

 

[Peter_SSs]

No problem

 

[Tigerexcel]

Leave it for the OP to answer, but for the benefit of others your solution doesn't appear to work in the above example, if we are correct in that each individual term is separated by a comma.

These are student results and on the basis of these results, student progression is determined. If a code has a prefix of 1 as in ABC1000, that indicates a preliminary unit and does not provide an entry into the next stage. A unit with a prefix of 2 or higher does allow entry but is specific to certain courses so that is why ABC2000 would open up more pathways providing the student has obtained at least a 50 (or P).
The information comes as a data dump with the scores separated by a comma and within a single cell. Depending on the level of analysis required and who is doing the job, I will at times transform the data with PQ but in this instance it was just a simple count that was needed so once the formula is created I can then onsend it to a junior staff member to process. PQ is great but no-one in my organisation is aware of it, simpler for me to enter a formula and copy down. One thing I love about Excel is that there are multiple ways of solving a problem, for me that has a big appeal.