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

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
491
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,383
Office Version
  1. 365
Platform
  1. Windows
.. so the example in post 18 was a valid example or not?
If valid, the correct count for ABC2*P* is ..?
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Watch MrExcel Video

Forum statistics

Threads
1,127,155
Messages
5,623,074
Members
415,950
Latest member
Kindz

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