Excel Advanced Filter not working with Wildcard (* asterisk) using regular numbers and hyphenated number system

Question

I have set up an advanced filter in Excel. I can not get the advanced filter to produce any output when using wildcard asterisks (*), using a source list that contains numbers and hyphenated numbers, OR alternatively when using a separate list which is producing a number from a formula. All adv.filter headers are properly placed and named; the filter works fine not using wildcards.

Original List:

• 10
• 10-1
• 11
• 11-1
• 100

I am using a formula to convert these hyphenated numbers to ignore the dashes so they will sort properly (smallest to largest) in my list:

=IF(A1="","",IFERROR(VALUE(LEFT(A1,FIND("-",A1)-1)),VALUE(A1)))

This results in a secondary column (which is the advanced filter source data):

• 10
• 10
• 11
• 11
• 100

My advanced filter criteria then becomes *10* with wildcards, with the output header being the original list with dashes included. I am expecting the wildcard to show both 10 and 100 in the output when user types in 10. There is no output data, however, when using the wildcard. There is exact match output data when I just type in 10 with no asterisks.

Any ideas for why wildcards aren't working? I tried formatting the source data and criteria data as both text, number, and general; none made any difference with wildcards. Thanks for the help!!!!

Answer 1

You had the right idea when you tried formatting as text: wildcards don't work on numeric values.

Where you're running into trouble is that formatting as text doesn't change numbers to text retroactively; only numbers entered after the format change get converted. Instead convert your data to strings first using the TEXT function in another location, and then filter that result.

Depending on exactly what you're doing with the VALUE function in your formula, you might even be able to use TEXT in there.