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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,789
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,789
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,789
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,118,855
Messages
5,574,677
Members
412,611
Latest member
MattDexx
Top