#1   Report Post  
Posted to microsoft.public.excel.misc
PL PL is offline
external usenet poster
 
Posts: 58
Default Auto filter

I have a column with numbers running from 1- 100, I used custom autofilter to
find all numbers beginning with 1 but it returned only all the empty rows,
why is that so?

Thanks
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 143
Default Auto filter

It has to do with the way that Excel views the numbers. Simply put, 100 is
100 versus a word, say 'dog', which can be broken up into d-o-g. Filter by
the first character of a cell, that cell must be a text value.

Assuming your number column runs from A2 to A101, you can put in a formula
in column B that converts those values to a text. In cell B2 enter:
="&A2 and copy that formula to the end of your range. Then do the filter on
column B and it should work.

Post back with questions.

--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"PL" wrote:

I have a column with numbers running from 1- 100, I used custom autofilter to
find all numbers beginning with 1 but it returned only all the empty rows,
why is that so?

Thanks

  #3   Report Post  
Posted to microsoft.public.excel.misc
PL PL is offline
external usenet poster
 
Posts: 58
Default Auto filter

Hi

Thanks . But what's the formula to convert numbers into text? I tried ="&A2,
it didn't work.

Thank you

"PJFry" wrote:

It has to do with the way that Excel views the numbers. Simply put, 100 is
100 versus a word, say 'dog', which can be broken up into d-o-g. Filter by
the first character of a cell, that cell must be a text value.

Assuming your number column runs from A2 to A101, you can put in a formula
in column B that converts those values to a text. In cell B2 enter:
="&A2 and copy that formula to the end of your range. Then do the filter on
column B and it should work.

Post back with questions.

--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"PL" wrote:

I have a column with numbers running from 1- 100, I used custom autofilter to
find all numbers beginning with 1 but it returned only all the empty rows,
why is that so?

Thanks

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Auto filter

I tried ="&A2, it didn't work.

Try it with 2 double quotes:

=""&A2

=A2&""


--
Biff
Microsoft Excel MVP


"PL" wrote in message
...
Hi

Thanks . But what's the formula to convert numbers into text? I tried
="&A2,
it didn't work.

Thank you

"PJFry" wrote:

It has to do with the way that Excel views the numbers. Simply put, 100
is
100 versus a word, say 'dog', which can be broken up into d-o-g. Filter
by
the first character of a cell, that cell must be a text value.

Assuming your number column runs from A2 to A101, you can put in a
formula
in column B that converts those values to a text. In cell B2 enter:
="&A2 and copy that formula to the end of your range. Then do the filter
on
column B and it should work.

Post back with questions.

--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"PL" wrote:

I have a column with numbers running from 1- 100, I used custom
autofilter to
find all numbers beginning with 1 but it returned only all the empty
rows,
why is that so?

Thanks



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel auto-filter does not filter certain columns Eric_NY Excel Discussion (Misc queries) 5 November 29th 08 10:13 AM
DataFilterAuto Filter in excel 2007? TIJ New Users to Excel 2 November 13th 08 03:28 AM
Excel 2007 Auto Filter Filter Django Excel Discussion (Misc queries) 2 September 9th 08 10:52 PM
Limit filter options in Auto Filter lista72 Excel Discussion (Misc queries) 1 January 23rd 08 04:01 PM
Excel auto filter doesn't recoginize case - won't filter AA from A Mikey Excel Discussion (Misc queries) 1 September 29th 05 08:18 PM


All times are GMT +1. The time now is 06:03 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"