Advanced Filter with Number as Text

user_6088149 0
Viewed 3457

I have a problem with advanced filter in Excel.

I will make it simple to let you understand and reproduce the same problem.

There is a Table T1 with data

Table T1

And a secondary Table T2 that is a clone of T1 but empty

I will use T2 for advance filtering T1

Table T2

Ill do it through a macro that i created, but you can simply click on advanced filter and select the second table after filling it up in the diagonal with the "searched value", i will use the SEARCH FIELD bar and a macro to this purpose and apply the filter with something like that:

T2.SetDiagonalValue val
T1.Range.AdvancedFilter xlFilterInPlace, T2.Range

Here is what happen if i write 572 in the Search Field

Table T1 Search 572

Table T2 Range

This is the intermediate step made by my macro:

Adv Filter

Result:

Result

And there is the problem:

In the T1 table i have some columns one of them have Postal Code (CAP) with cells formatted as Text but written with only numbers.

If i use Advanced Filter to search Text, it will work: example

Table T1 Search Text

Table T2 Data

But if i try to search NUMERIC VALUE it will work only if the searched value match entire cell contents, and PARTIALLY WORK (he will show only result of number in cells formatted as TEXT, the cell with yellow background is formatted as NUMBER) if i place a Jolly character in the search field:

Not Working:

Not Working

Not Showing yellow cell:

Partially Working

Showing all:

Working

Instead if a Cells is Text and start with number, he will work only if i place the entire starting number and at least 1 char after it:

Searching for 622SVCB, Not Working:

Not Working

Working:

Working

Each column has been set as "TEXT" except the cells with Yellow Background. Why this happen?

vba excel
2 Answers

When approaching the Advanced Filter in Excel, try to keep a few things in mind:

  • The expression for Criteria range should evaluate to TRUE or FALSE
  • When entering a number in Criteria range, Excel is going to look for that exact number (not higher, not lower), regardless of how the number is formatted on the spreadsheet
  • Excel assumes the Criteria range is preceded with = unless the filter begins with a different operator (i.e. >, <, >=, <=, <>)
  • Regardless of how TableT1 is formatted, Excel is going to view the Criteria range exactly as typed. For example, if the Criteria range is all numbers (e.g. 123), Excel is going to look for a numerical match. If the Criteria range is all letters or a combination of letters and numbers (e.g. abc or abc123), Excel is going to compare based on text characters.

From what I can tell, your filter isn't working on partial numbers because you're essentially asking Excel to find that exact number, which doesn't exist in your "database". If you precede the criteria in TableT2 with an operator, you should get the results you're seeking.

Filter criteria (TableT2)
Filter criteria

Filtered results (TableT1)
Filtered results

user_64783630

I will try to answer your question:

  1. even though all your table is format as text, the highlighted field in yellow is obvious still a number, as it is still aligned to the RIGHT

  2. the criteria matrix is applied as a text filter (something like the screenshot 1, but NOT as screenshot 2). and a text filter will not apply to a number unless it is exact match enter image description here

enter image description here

To solve your issue:

  1. add a line of code to the table to force every column to text before apply the filter
  2. add * to all search value so that it is read as text as well

Code

T2.SetDiagonalValue val & "*"

'sample code to force the column to TEXT 

Range("Table1[[#All],[TEXT WITH NUMBER]]").TextToColumns 
Destination:=Range("Table1[[#Headers],[TEXT WITH NUMBER]]"), DataType:=xlFixedWidth, 
FieldInfo:=Array(0, 2)

'end of sample code

T1.Range.AdvancedFilter xlFilterInPlace, T2.Range

here is the result i get:

enter image description here

Hope that this would help

user_89849690
Related