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

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.


terrapinie wrote:
>
> 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

That worked Dave!! Thanks!!