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 a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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)?
 
Upvote 0
rawP textCount
ABC2020:P:57, ABC2040:*P*:53, ABC2050:P:58, ABC2080:N:48, P3

I repeat Peter's question: why 2 not 3 ?
 
Upvote 0
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
AB
1ResultsFormula should return
2ABC2020:P:57, ABC2040:P:53, ABC2050:P:58, ABC2080:N:48, 3
3ABC2020:P:50, ABC2040:P:54, ABC2050:N:34, ABC2080:N:9, 2
4ABC2020:P:56, ABC2040:N:48, ABC2050:N:48, ABC2080:N:44, 1
5ABC2020:C:63, ABC2040:N:36, ABC2050:N:43, 0
6ABC2020:D:76, ABC2040:P:55, ABC2050:C:67, 1
7ABC2020:D:72, ABC2040:P:52, ABC2050:P:52,2
8ABC1010:D:75, ABC1020:C:69, ABC1110:HD:80, ABC2100:P 57, 1
Sheet1
 
Upvote 0
you can try to add column with: List.Count(Text.Split([raw],"P"))-1

rawFormula should returnSCount
ABC2020:P:57, ABC2040:P:53, ABC2050:P:58, ABC2080:N:48, 33
ABC2020:P:50, ABC2040:P:54, ABC2050:N:34, ABC2080:N:9, 22
ABC2020:P:56, ABC2040:N:48, ABC2050:N:48, ABC2080:N:44, 11
ABC2020:C:63, ABC2040:N:36, ABC2050:N:43, 00
ABC2020:D:76, ABC2040:P:55, ABC2050:C:67, 11
ABC2020:D:72, ABC2040:P:52, ABC2050:P:52,22
ABC1010:D:75, ABC1020:C:69, ABC1110:HD:80, ABC2100:P 57, 11
 
Upvote 0
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.
 
Upvote 0
Hi Sandy,

I was hoping to solve this with an Excel function rather than PQ if possible.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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
Back
Top