All data disappears when using autofilter
 

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

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


--
apandbp
------------------------------------------------------------------------
apandbp's Profile: http://www.thecodecage.com/forumz/member.php?userid=550
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118775

All data disappears when using autofilter
 

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........................



"apandbp" wrote in message
...

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

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


--
apandbp
------------------------------------------------------------------------
apandbp's Profile: http://www.thecodecage.com/forumz/member.php?userid=550
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=118775

All data disappears when using autofilter
 

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

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

"Ken Wright" wrote in message
...
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........................



"apandbp" wrote in message
...

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

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


--
apandbp
------------------------------------------------------------------------
apandbp's Profile:
http://www.thecodecage.com/forumz/member.php?userid=550
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=118775

All data disappears when using autofilter
 

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

Ken Wright;427495 Wrote:
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........................



"apandbp" wrote in message
...

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

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


--
apandbp

------------------------------------------------------------------------
apandbp's Profile: 'The Code Cage Forums - View Profile: apandbp'
(http://www.thecodecage.com/forumz/member.php?userid=550)
View this thread:
'All data disappears when using autofilter - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=118775)



--
apandbp
------------------------------------------------------------------------
apandbp's Profile: http://www.thecodecage.com/forumz/member.php?userid=550
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118775

All data disappears when using autofilter
 

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.
--
David Biddulph

"apandbp" wrote in message
...

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

Ken Wright;427495 Wrote:
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........................



"apandbp" wrote in message
...

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

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


--
apandbp

------------------------------------------------------------------------
apandbp's Profile: 'The Code Cage Forums - View Profile: apandbp'
(http://www.thecodecage.com/forumz/member.php?userid=550)
View this thread:
'All data disappears when using autofilter - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=118775)



--
apandbp
------------------------------------------------------------------------
apandbp's Profile: http://www.thecodecage.com/forumz/member.php?userid=550
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=118775

All data disappears when using autofilter
 

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

hole will help

--
Basilio


"apandbp" wrote:


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

Ken Wright;427495 Wrote:
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........................



"apandbp" wrote in message
...

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

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


--
apandbp

------------------------------------------------------------------------
apandbp's Profile: 'The Code Cage Forums - View Profile: apandbp'
(http://www.thecodecage.com/forumz/member.php?userid=550)
View this thread:
'All data disappears when using autofilter - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=118775)



--
apandbp
------------------------------------------------------------------------
apandbp's Profile: http://www.thecodecage.com/forumz/member.php?userid=550
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118775

All data disappears when using autofilter
 

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.


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

hole will help

--
Basilio