Using Autofilter in Excel 2003 to show a group of numbers based on a single criteria

[jurroppi1]

I'm curious if there is a way to use Autofilter in Excel 2003 to show a group of numbers based on a single criteria. For example I'm looking for only the numbers that begin with 12 and my list is as follows:

1200
1203
1205
1216
1289
1300
1102
1502

I want all of the numbers that are 1200 - 1299 but I want to use one custom criteria (such as "contains 12" or use a wildcard, etc...) instead of: Filter>AutoFilter>Custom>is greater than 1199>and>is less than 1300.

Is this possible and if not, why not?

I would prefer an explanation as to why it doesn't work so I understand the issue rather than having to just accept it on faith that there's no other way to do this than using greater than and less than.

 

[AlphaFrog]

You could do it if your numbers were Text values and not Numeric values.

This example code converts column A to text and autofilters on Contains 12

With Columns("A:A")
     .TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
                                 FieldInfo:=Array(0, 2), TrailingMinusNumbers:=True
        
    .AutoFilter Field:=1, Criteria1:="=*12*"
End With

 

[jurroppi1]

I take it that all of the other custom autofilter options assume/require text formats vs. numeric and this is the reason why it does not work the way I want it to (without first converting the numeric values to text as you suggest).

Is that a correct understanding of the function?

 

[AlphaFrog]

I take it that all of the other custom autofilter options assume/require text formats vs. numeric and this is the reason why it does not work the way I want it to (without first converting the numeric values to text as you suggest).

Is that a correct understanding of the function?

No, I wouldn't say that.

You had requested to find "12" while not using the numeric criteria >=1200 and <=1299. Converting the numbers to text is a way to achieve that. Your requested filter criteria dictates the need to convert the numeric values to text.

 

[jurroppi1]

No, I wouldn't say that.

You had requested to find "12" while not using the numeric criteria >=1200 and <=1299. Converting the numbers to text is a way to achieve that. Your requested filter criteria dictates the need to convert the numeric values to text.

Actually I requested numbers that <U>begin</U> with 12 (specifically those that were between 1200 and 1300) - greater than 1199 and less than 1300 without having to resort to using that as my criteria in a custom Autofilter set.

Basically how do I do that with one criterion using either "contains" with a wildcard, or "begins with" when trying to filter numerical values in a list. If that was possible, and if not, why...

 

[AlphaFrog]

I guess I would say that within the Autofilter criteria, the only two wildcards are * and ? and they are both text-only wildcards. There doesn't seem to be any numeric wildcards available.

 

[jurroppi1]

O.K. again this gets back to my original final question of why it seems to require >< for numerical values to work and why I simply can't use "begins with 12" as my custom filter criteria when looking for only results >=1200 and <1300 in a numerical list as originally proposed.

As originally stated:
"I want all of the numbers that are 1200 - 1299 but I want to use one custom criteria (such as "contains 12" or use a wildcard, etc...) instead of: Filter>AutoFilter>Custom>is greater than 1199>and>is less than 1300.

Is this possible and if not, why not?

<B>I would prefer an explanation as to why it doesn't work so I understand the issue rather than having to just accept it on faith that there's no other way to do this than using greater than and less than."</B>

I do appreciate the quick responses very much though...

 

[Pigankle]

You can't filter based on the visual formatting of the value, because it isn't being stored/handled based on it's formatting.

Internally in Excel, numbers are not stored as "1234.5" They are floating point doubles. Internally, 1234 =1.00110100101 * 2^10 (Or 00 00 00 00 00 4A 93 40, depending on what you mean by "internally") Autofilter doesn't know how the number looks to you - only what its value is. Although this lack of knowledge gets in the way of your desired operation, it has performance benefits for any operations that actually treat numbers like numbers, rather than string representations.

 

[jurroppi1]

You can't filter based on the visual formatting of the value, because it isn't being stored/handled based on it's formatting.

Internally in Excel, numbers are not stored as "1234.5" They are floating point doubles. Internally, 1234 =1.00110100101 * 2^10 (Or 00 00 00 00 00 4A 93 40, depending on what you mean by "internally") Autofilter doesn't know how the number looks to you - only what its value is. Although this lack of knowledge gets in the way of your desired operation, it has performance benefits for any operations that actually treat numbers like numbers, rather than string representations.

O.K. I do get that the numbers are stored this way (floating point doubles) and I do understand what they are since I do program in a few languages. I just didn't realize that the autofilter function was incapable of doing same with said criteria as I wanted it to be.

I guess it becomes more obvious once you think about having to deal with the potential for both text and numerical values. Certain custom filter sets would be directly related to numerical value input such as ><, while others would have to *potentially* handle either text or numerical as a variant type, but it appears as though the other autofilter options are extremely limited in this respect.

Am I way off here?