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]

number of times ABC2*P* occurs, which in this case is 2.

ABC2020:P:57, ABC2040:P:53, ABC2050:P:58, ABC2080:N:48,

If the answer is 2, which of the highlighted value are you not wanting to count and why?

With 300+ posts you should be getting the idea that one example is not enough to go on and you force helpers to guess or ask questions like this before consideration can be given ..

1. Is there always 4 terms in each cell like your one example or can that vary?
2. Are the numbers at the end always exactly 2-digits like your one example?
3. Are all terms in the cell exactly 12 characters like your one example?
4. Do all cell terms start with ABC2 like your one example?
5. Do all cells actually end with a comma like your one example?

So what about 6-8 examples that show any variations possible (and the expected results)?

 

[sujeetsamal]

=(LEN(LARGE TEXT)-LEN(SUBSTITUTE(LARGE TEXT,TEXT TO SEARCH,"")))/LEN(TEXT TO SEARCH)

 

[sandy666]

raw		P text	Count
ABC2020:P:57, ABC2040:*P*:53, ABC2050:P:58, ABC2080:N:48,		P	3

I repeat Peter's question: why 2 not 3 ?

 

[Peter_SSs]

=(LEN(LARGE TEXT)-LEN(SUBSTITUTE(LARGE TEXT,TEXT TO SEARCH,"")))/LEN(TEXT TO SEARCH)

Have you tried that with wildcards as the OP has asked?

 

[Tigerexcel]

In my haste to post I put up the wrong example, sorry that should have read 3 not 2 in the opening post, sorry.
I've added some extra data that may help.

MEB Student Performance (2).xlsx
	A	B
1	Results	Formula should return
2	ABC2020:P:57, ABC2040:P:53, ABC2050:P:58, ABC2080:N:48,	3
3	ABC2020:P:50, ABC2040:P:54, ABC2050:N:34, ABC2080:N:9,	2
4	ABC2020:P:56, ABC2040:N:48, ABC2050:N:48, ABC2080:N:44,	1
5	ABC2020:C:63, ABC2040:N:36, ABC2050:N:43,	0
6	ABC2020:D:76, ABC2040:P:55, ABC2050:C:67,	1
7	ABC2020:D:72, ABC2040:P:52, ABC2050:P:52,	2
8	ABC1010:D:75, ABC1020:C:69, ABC1110:HD:80, ABC2100:P 57,	1
Sheet1

 

[sandy666]

you can try to add column with: List.Count(Text.Split([raw],"P"))-1

raw	Formula should return	SCount
ABC2020:P:57, ABC2040:P:53, ABC2050:P:58, ABC2080:N:48,	3	3
ABC2020:P:50, ABC2040:P:54, ABC2050:N:34, ABC2080:N:9,	2	2
ABC2020:P:56, ABC2040:N:48, ABC2050:N:48, ABC2080:N:44,	1	1
ABC2020:C:63, ABC2040:N:36, ABC2050:N:43,	0	0
ABC2020:D:76, ABC2040:P:55, ABC2050:C:67,	1	1
ABC2020:D:72, ABC2040:P:52, ABC2050:P:52,	2	2
ABC1010:D:75, ABC1020:C:69, ABC1110:HD:80, ABC2100:P 57,	1	1

 

[Peter_SSs]

Sorry, I forgot (at least) one more question
6. What would be the most number of terms in a single cell? 4 is the maximum in your samples so far.

 

[Tigerexcel]

Hi Sandy,

I was hoping to solve this with an Excel function rather than PQ if possible.

 

[Tigerexcel]

Sorry, I forgot (at least) one more question
6. What would be the most number of terms in a single cell? 4 is the maximum in your samples so far.

I've looked at the first 1,000 records of a 10,000 record data set and so far nothing exceeds 4 results so pretty safe bet that 4 is maximum.