filtering based on pattern

Electriasta

New Member
Joined
Apr 24, 2013
Messages
1
Hello!

I'm trying to filter a list of numbers based on pattern.

For instance if I want to have only the cells containing values that start with two sets of three identical numbers.

so that if my list contains two cells having "11100022" and "22233389" they will show up since both start with two sets of three identical numbers.

knowing this will allow me to filter using patterns :)


I would highly appreciate any advice!

thanks..
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

rjwebgraphix

Well-known Member
Joined
May 25, 2010
Messages
588
This drove me a little batty today, but I got it figured out for you.

Everything you need is covered here...

Excel Advanced Filter -- Complex Criteria

...but I will explain in further detail so you don't have the same difficulty I did.

Advanced Filter will do the job for you. You can access advanced filter by clicking Data then Filter then Advanced Filter, but before you do that there are some other things that need to be done.

Here is the test data that I used.
Excel Workbook
AB
1dummyHDRNumbers
2This11100022
3column22233389
4doesn't65461000
5matter46432156
6only22246816
7here46546544
8for65461651
9test11198746
10purposes65465160
Sheet


As you can see, some rows have your 111 or 222 pattern.

Now you have to set up the criteria in which you want to filter. I suggest using cells that are outside the data range so that the filtered data will not conflict with the visibility of the criteria. In this case, with the data block ending at B10, I will start my criteria at D12. 2 cells right and 2 cells down from the data block, but in all actuality you can put the criteria anywhere.

This is how I set up the criteria in which Advanced Filter will use to filter the data...
Excel Workbook
D
12
13TRUE
14FALSE
Sheet


Now just a bit ago I mentioned starting my criteria in cell D12, but as you can see D12 is Empty. This is what drove me crazy for a bit. You might think that your criteria starts in D13 because D12 is empty, although, D12 represents the header row. When filtering with a Formula, which is what this is, you must leave the header row blank, but the Header Row MUST be included in your criteria range or you will get no results of the filter.

Now lets look at the formulas in the criteria range...

Isnumber is simply going to produce a True or False. When using a formula as your criteria, the formulas can only produce a true or false. If they produce a value, it won't work.

Find is just finding whatever number pattern you are looking for and obviously whatever is in the quotes is the number you are looking for.

The cell reference, in this case is B2 for both formulas, should always point to the top most cell containing the data, but not the header row. That is why they are both B2.

Now that we have our criteria set, its time to run the advanced filter, however, before you do it, it would be easiest if you highlight your entire data block that you want to filter, but do not highlight what you set into cells for criteria.

With the data block highlighted
Click Data - Filter - Advanced Filter

You'll notice that List Range is already filled out for you. This is because you highlighted the data block first. You don't have to highlight first, but it can save time.

For now just leave it set to "Filter the list, in place"
Click in the Criteria Range box, now you can set your criteria range. In this case we want to set the criteria range to $D$12:$D$14. Remembering that even though D12 is empty, it still acts as the header row. Without setting the empty row too you will produce no results.

leave Unique Records unchecked.

Click ok and the results should look like this....
Excel Workbook
AB
1dummyHDRNumbers
2This11100022
3column22233389
6only22246816
9test11198746
Sheet


There you go. A step by step breakdown of filtering numbers by a pattern in the number.

However, if there are any letters in your pattern at all, then this is all moot as there's an easier way to do it if the cell contents contain text.

Hope this helps,
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Electriasta, welcome to the MrExcel board!


.. I got it figured out for you.
Possibly, but I interpret the requirement differently ..


For instance if I want to have only the cells containing values that start with two sets of three identical numbers.

so that if my list contains two cells having "11100022" and "22233389" they will show up since both start with two sets of three identical numbers.
If my interpretation is correct, then for your sample data, rows 6 and 9 should not be visible since those values start with one set of three identical numbers only.

My suggestion also uses Advanced Filter. Note that I have changed rjwebgraphix's sample data a little.

My sample data, and Advanced Filter formula in D2 (D1 empty)

Excel Workbook
ABCD
1dummyHDRNumbers
2This11100022TRUE
3column22233389
4doesn't65461000
5matter46432156
6only22222216
7here46546544
8for65461651
9test11199946
10purposes65465160
11
Advanced Filter



Apply AdvancedFilter to A1:B10 with Criteria range D1:D2.

Results of the filter:

Excel Workbook
AB
1dummyHDRNumbers
2This11100022
3column22233389
6only22222216
9test11199946
11
Advanced Filter
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
.. I got it figured out for you.
I forgot to mention one other thing about your suggestion:
It would show rows that contained "111" or "222" anywhere in the number, not just at the start.
 

rjwebgraphix

Well-known Member
Joined
May 25, 2010
Messages
588
Possibly, but I interpret the requirement differently ..

=LEFT(B2,6)=REPT(LEFT(B2,1),3)&REPT(MID(B2,4,1),3)

You're probably right. I'm not entirely sure the pattern is clear, but having the basic concept of how to do it, which was my goal, the rest is merely having the correct formula criteria. It looks like each pattern needed could, in the end, have vastly different formula criteria.

Electriasta, if the criteria formula's don't work out for you as needed, feel free to followup with more detail how the pattern is laid out and the desired result.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,814
Members
414,104
Latest member
imamalidadashzada

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