Auto Filter returns no results

[terrapinie]

I have a spreadsheet with about 11 columns and 800 rows. I have all
columns ready for auto filter. The last column (K) has numerical
values in it, and I would like to search for all rows that contain a
certain number. When I do this, it returns no results - even when I
know that a number is listed. If I select to show all that 'equal' the
number, it will return the result, but not when filtering for
'contains'. [I need to use 'contains' because some cells have more
than one number in them (separated by commas).]
I have Office Professional 2003 - Excel 2003 - SP2.

Any suggestions?

Thanks,
Laurie

 

[Dave Peterson]

If excel sees your numbers as real numbers, like:
123,456,789
Then contains won't work.

But if you've formatted the cell/column of cells first as Text, then did the
data entry, excel will see them as text and contains will work.

One sneaky way around it is to use a helper column:

=a2&""

By appending that null string "", that cell will be treated as text and contains
will work.

I have a spreadsheet with about 11 columns and 800 rows. I have all
columns ready for auto filter. The last column (K) has numerical
values in it, and I would like to search for all rows that contain a
certain number. When I do this, it returns no results - even when I
know that a number is listed. If I select to show all that 'equal' the
number, it will return the result, but not when filtering for
'contains'. [I need to use 'contains' because some cells have more
than one number in them (separated by commas).]
I have Office Professional 2003 - Excel 2003 - SP2.

Any suggestions?

Thanks,
Laurie

 

[terrapinie]

That worked Dave!! Thanks!!