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

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
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
45,193
Office Version
365
Platform
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,468
Platform
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,778
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,468
Platform
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
45,193
Office Version
365
Platform
Windows
@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,468
Platform
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
379
Office Version
2016
Platform
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,351
Messages
5,486,363
Members
407,542
Latest member
Tyronaught

This Week's Hot Topics

Top