Custom Auto Filter

[Martin]

Previously posted in microsoft.public.excel.worksheetfunctions but I
didn't get any response, so I'm trying another group in case I posted
to the wrong one. On the other hand, if I've not been clear then
please ask for clarification as I'm very keen to find the answer to
this and I have no idea what to try now.


Using Excel 2000.

I have a column, formatted as text, with the following:

Heading
02
01
05
04
03

(that's zero 1, zero 2 etc).

Ultimately I want to filter the values to show those greater then xx
and less than yy. However, for now I'm just trying to get the first
part to work.

I have tried using Custom filter :
If I enter Equals 02, all is well. However, if I specify Is greater
than 02, no rows are returned and then when I go back into the Custom
Filter dialogue I find that Excel has reset my value to be just 2.

In order to get round this I have tried putting the 02 inside quotes
but then all the rows are returned because a quote comes before a zero
in the sort sequence.

This seems as if it should be really simple, but I'm stuck. Any help
would be very appreciated.

Martin

 

[JulieD]

Hi Martin

any reason they're formatted as text? could you have them as numbers? then
the comparison that you're seeking would work quite easily.

Cheers
JulieD

 

[CLR]

Hi Martin..........

Instead of Is greater than 02, type it in with a leading apostrophe, as
'02...........

Vaya con Dios,
Chuck, CABGx3

 

[Michael]

Hi Martin
Have you tried using a formula.
If your data is in A1, try:
=IF(AND(A1>"00",A1<"04"),A1,"")in A2 and copy down as
required.
This will then list everything greater than and, in this
case, less than 00 and 04 respectively.
Regards
Michael

-----Original Message-----
Previously posted in

microsoft.public.excel.worksheetfunctions but I

 

[Martin]

Thank-you everybody! I appreciate you taking the trouble to help me.

Julie, the reason that the data is formatted as text is that the real
data I shall be using is UK telephone numbers, so the leading zeroes
are an integral part of the data.

Michael, your formula works great.

Chuck, your idea works great too, and is so simple. I'm puzzled about
_why_ it works and I really want to understand! Is it a work-around
or is it the 'official' way to do it?

Martin

 

[CLR]

Hi Martin...........

You're welcome, and thanks for the feedback............I'm glad you got a
couple of workable solutions..............that's the beauty of this
system......you usually get several different solutions to try to fit to
your application............as for "why" my offering works, I dunno the
details,.......it's just that numbers with leading zeros exist in Excel only
as TEXT, and by typing the leading apostrophe it forces TEXT on the entry
rather than having Excel convert 02 back to 2 as a number...............

Vaya con Dios,
Chuck, CABGx3