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

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
493
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
1ABC2020: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.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,485
Platform
  1. MacOS
The OP stated to search for ABC2*P, that example has ABC2*N & ABC1*P, so the result would be 0.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,420
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 29, 2003
Messages
6,485
Platform
  1. MacOS
@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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497

ADVERTISEMENT

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

Well-known Member
Joined
Apr 29, 2003
Messages
6,485
Platform
  1. MacOS
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,420
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@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
AB
1ResultsCount
2ABC2020:D:72, ABC2040:P:52, ABC2050:P:52,2
Sample
Cell Formulas
RangeFormula
B2B2=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

Well-known Member
Joined
Apr 29, 2003
Messages
6,485
Platform
  1. MacOS
Brilliant, thanks for taking the time to explain.

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

Many thanks.

Gaz
 

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
493
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,323
Messages
5,675,089
Members
419,549
Latest member
EliteBeat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top