multiple wild cards

silent bob

New Member
Joined
Jun 22, 2005
Messages
15
Can any one help me with this one:

I am trying to query a large data table in column A I have a code that represents different elements of each record. The code look something like

010010110

The 1’s or 0’s refers to a different elements of each record ie the first digit might mean that a client is female the second that they are based in the uk .

What I want to do is run a query that might like count the number of records for clients based in the uk ie:

=countif(A1:A10000,”*1*******”)

I could then make more complex queries like

=countif(A1:A10000,”*1**0**01”)

Thing is that the when I use multiple wild cards like ***0** to try to pick out the 4th digit excel returns codes like 101111 rather than 111011.

Is there a way to lock down the witch digit within a number I want the wild card to apply to.

I really shouldn’t attempt these things last thing on a Friday.

Thanks
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
You could perhaps try:

=COUNTIF(A1:A10000,"???0??")

(Assumes cells are formatted to Text given possibility of leading 0's)
 
Upvote 0
Hello silent bob,

an * wildcard represents any number of characters (including zero) so it doesn't make sense to have 2 together. Try ? wildcard which represents a single character, e.g.

=countif(A1:A10000,"?1??0??01")

A1:A10000 needs to be text formatted
 
Upvote 0

Forum statistics

Threads
1,206,969
Messages
6,075,918
Members
446,169
Latest member
luckyfind4u

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