All data disappears when using autofilter

[apandbp]

This one has me truly beat. I have a long list of numbers, and I nee
to filter the ones out that have a zero as the second digit from th
left. I have been using auto filter>custom and tried using wild cards
and have had no success. I have included some sample data below. Yo
can also find a copy of the actual xls sheet a
www.ideaharvesters.com/testxls.xls

Account
00616946
03745254
03745254
10043826
13635685
20071601
20560611
20560611
20560611
23474810
3043787

 

[Ken Wright]

So just use a helper column. In cell E2 put this and copy down:

=MID(TEXT(A2,"00000000"),2,1)="0"

Now pop in a header and filter on TRUE or FALSE depending on what you want.

Your problem stems from the fact that your data is set to display the number
616946 as 00616946, so what you see isn't actually what you have, so you
will find it hard to filter on those 0s when they don't exist.

The TEXT function will simply ensure that what you actually see is returned
as a value, and then the MID function simply allows you to focus on that
second character. Note it is now TEXT so hence the quotes round the 0.

Regards
Ken........................

 

[Ken Wright]

Sorry, make that D2 not E2 - Didn't even see your formulas in there.

Regards
Ken.....................

 

[apandbp]

WOW! That is truly amazing. It worked perfectly. Thank you so much for
your help. Would you mind explaining briefly what exactly that formula
(=MID(TEXT(A2,"00000000"),2,1)="0") does?

Kindest Regards,
Anthony

 

[David Biddulph]

The functions MID and TEXT are both standard Excel functions, and their
syntax and operation can be found in the Excel help for the function
concerned.

TEXT(...,"00000000") turns the number 616946 into a text string 00616946.
MID(...,2,1) takes one character, starting at the second character of the 8
character string, so it extracts the second 0.
The formula then checks whether that character is indeed 0, and returns TRUE
if it is, or FALSE if it isn't.

 

[basilio]

to me wild cards worked perfectly
just enter your data as text and then <in filter> try :
" equals " ?0*

hole will help

 

[Ken Wright]

Of course if you read the rest of the replies you will realise that he does
not actually have the data he thinks he has, so no wildcard is going to help
as it cannot find what is not actually there to begin with, so hence the use
of the TEXT function to actually give him what he already thought he had,
after which any of his normal filtering methods should now work.