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.

 

[Tigerexcel]

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

This is a very useful formula but I couldn't get it to work with wildcards.

 

[sandy666]

Hi Sandy,

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

sure, it was for others if they want to see another approach

 

[gaz_chops]

Try

=SUM(ISNUMBER(SEARCH($D$2,REPLACE(A2,15,99,""),1)),ISNUMBER(SEARCH($D$2,REPLACE(A2,29,99,""),15)),ISNUMBER(SEARCH($D$2,REPLACE(A2,43,99,""),29)),ISNUMBER(SEARCH($D$2,A2,43)))

D2 = ABC2*P

 

[Peter_SSs]

This allows for up to 10 9 per cell. It appears fro your samples (here & in previous threads) that there is always 8 characters before the P/N/HD etc

20 07 23.xlsm
	A	B	C
1	Results	Count	Formula should return
2	ABC2020:P:57, ABC2040:P:53, ABC2050:P:58, ABC2080:N:48,	3	3
3	ABC2020:P:50, ABC2040:P:54, ABC2050:N:34, ABC2080:N:9,	2	2
4	ABC2020:P:56, ABC2040:N:48, ABC2050:N:48, ABC2080:N:44,	1	1
5	ABC2020:C:63, ABC2040:N:36, ABC2050:N:43,	0	0
6	ABC2020:D:76, ABC2040:P:55, ABC2050:C:67,	1	1
7	ABC2020:D:72, ABC2040:P:52, ABC2050:P:52,	2	2
8	ABC1010:D:75, ABC1020:C:69, ABC1110:HD:80, ABC2100:P 57,	1	1
Count in Cell

Cell Formulas
Range	Formula
B2:B8	B2	=SUMPRODUCT(--(LEFT(TRIM(MID(SUBSTITUTE(", "&A2,", ",REPT(" ",100)),{1,2,3,4,5,6,7,8,9}*100,100)),4)="ABC2"),--(MID(TRIM(MID(SUBSTITUTE(", "&A2,", ",REPT(" ",100)),{1,2,3,4,5,6,7,8,9}*100,100)),9,1)="P"))

 

[gaz_chops]

Note: my solution only allows for 4 per cell.

 

[sandy666]

just for fun

note: my solution doesn't care how many elements are there, just counting

 

[Peter_SSs]

It appears from your samples (here & in previous threads) that there is always 8 characters before the P/N/HD etc

If that is so, a simpler formula would be

20 07 23.xlsm
	A	B
1	Results	Count
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
9	ABC2020:N:40, ABC1111:P:55	0
Count in Cell

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

 

[Peter_SSs]

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

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

 

[Tigerexcel]

Thanks Gaz, Peter and Sandy. I spent a good deal of time today trying to come up with a formula and was unsuccessful, almost thought it couldn't be done via a formula. I won't pretend that I understand the formulae completely but at this stage it's easier to enter that on other users' spreadsheet rather than explaining PQ to them.

 

[Peter_SSs]

You're welcome. Glad we could help.