advanced filter, but only pick up security ID that doesnt start with a number

daveyc18

Active Member
Joined
Feb 11, 2013
Messages
420
so i want to filter data, but i only want sec. ID that begins with a letter (or in other words, not a number).
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,118
Office Version
365
Platform
Windows
You could use a "helper" column, to determine if it starts with a number or not, like this (for an ID in cell A2):
=ISNUMBER(LEFT(A2,1)+0)
Ones that start with a number will return TRUE, and ones that don't will return FALSE, and then you can use this column to filter on.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,527
Office Version
365
Platform
Windows
Since you are apparently using Advanced Filter, it depends on how you are doing the Adv Filter. That is
- manual or vba
- Headings with criteria or a no-heading formula approach?

Here is an example of a no-heading formula approach.
Formula as shown goes anywhere that has a vacant cells above it. note that the formula points to the first data cell in the sec. ID column only.

A_MrExcel.xlsm
CDEFGH
1
2FALSE
3
4Hdr 1sec. IDHdr 3Hdr 4
5data3824datadata
6dataH35datadata
7data65025datadata
8dataXD87865Rdatadata
Adv Fltr
Cell Formulas
RangeFormula
H2H2=NOT(ISNUMBER(LEFT(D5,1)+0))



Then do an Advanced Filter on the range C4:F8 with a criteria range of H1:H2. Result:

A_MrExcel.xlsm
CDEFGH
1
2FALSE
3
4Hdr 1sec. IDHdr 3Hdr 4
6dataH35datadata
8dataXD87865Rdatadata
9
Adv Fltr
Cell Formulas
RangeFormula
H2H2=NOT(ISNUMBER(LEFT(D5,1)+0))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,527
Office Version
365
Platform
Windows
using VBA advanced filter relying on headers
In that case could we see the code you are currently using and some sample data so we can see the layout and where/how you are using those headers in your criteria?
 

Forum statistics

Threads
1,089,640
Messages
5,409,479
Members
403,265
Latest member
HMR120

This Week's Hot Topics

Top