MRDecarte

New Member
Joined
May 24, 2014
Messages
20
How would you set up advanced filter if you want to collect records of a dataset that contain numbers stored as text, and you want them starting with the partial text 646&"*" or 647&"*". I have a dataset where a column contains account code in text format such 6****** and I want all of those records starting with 646 and 647.

I know how to do it with custom filter in the column using begins with or begins with but I cannot get it to work using advanced filter.

Thank you
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,937
Office Version
  1. 365
Platform
  1. Windows
Did you try: 646*
if your number is stored as text (not just formatted as text) then it should work.
 

MRDecarte

New Member
Joined
May 24, 2014
Messages
20
Thanks Akuini but it does not work. The number are stored as text I believe as aligned to the left and when using the function ISTEXT I get a TRUE.
I get all account codes 643...647 etc unfortunately. Thanks though.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,937
Office Version
  1. 365
Platform
  1. Windows
Not sure why it didn't work.
Here's what I got using this code:

Code:
Sub Macro11()
    Range("Table1[#All]").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
        :=Range("A1:D2"), Unique:=False
End Sub


Excel 2013 32 bit
A
B
C
D
1
STATECITYREPno
2
1*
3
4
5
6
7
STATECITYREPno
8
ArkansasConwayQuincy1
17
ArkansasSpringdaleTruman10
18
ArizonaTucsonMario11
19
ArizonaTucsonMark12
20
ArkansasJonesboroMakai13
21
CaliforniaChula VistaZachariah14
22
CaliforniaChula VistaZander15
Sheet: Sheet1
 

MRDecarte

New Member
Joined
May 24, 2014
Messages
20
Great many thanks for the macro and solution! It is a nice peace of code :)
I can get it to work with the following formula but I find it strange that it is not working with your initial proposal 646* or 647* with the corresponding field name included in the advanced filter procedure.

OR(LEFT(I3,3)="646",LEFT(I3,3)="647")

Many thanks Akuini
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,937
Office Version
  1. 365
Platform
  1. Windows
Hm, it's strange you can't get it to work.
I'm using Excel 2013, what's yours?
But did you download my file & tried it, does it work?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,829
Messages
5,598,341
Members
414,232
Latest member
MIA10_KO

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