Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AutoFilter No Work (2000)

    <P ID="edit" class=small>(Edited by mitjones on 25-Nov-04 02:57. )</P>I have a list of imported data with the following criteria I want to filter on: 03-89-0000-9203-909 and 03-89-0000-9203-910. So, I select AutoFilter, the custom and plug into the blanks using either the OR or AND (and DOES NOT EQUAL) and it continues to show the data containing the above. I have this column formatted in custom format as follows: 00-00-0000-0000-000.

    I have noticed on the formula bar that some of the ones the filter doesn't seem to pick up do NOT have the hyphens in them. For example, they will appear as 38900009203809.

    Any suggestions?

    Thanks.

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: AutoFilter No Work (2000)

    See the attached workbook for a simulation of your query!

    If you want to filter for all the 03-89-0000-9203-909 and 03-89-0000-9203-910 numbers, then use:
    Equals 03-89-0000-9203-909 OR Equals 03-89-0000-9203-910

    If you want to filter for all the non 03-89-0000-9203-909 and 03-89-0000-9203-910 numbers, then use:
    Does Not Equal 03-89-0000-9203-909 AND Does Not Equal 03-89-0000-9203-910

    The codes that show the hyphens in the formula bar were pasted from a text file or added to the sheet using some other method. Its probably seen as a text entry by excel. If you have type other numbers into the sheet directly without typing the hyphens, then the hyphens do not show in the formula bar, as they do not exist in the cells. They are then just view on screen as a formatted display character!
    You will notice that if you delete the hyphens from the codes that show them in the formula bar, and then enter, the hyphens remain, in the cell as display, but will not appear after that in the formula bar!
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilter No Work (2000)

    The cells that do not show the dashes in the formula bar probably do not have the dashes entered. The cells where the dashes show in the formula bar had the dashes typed in when the value was entered and are therefore in the cells as text values. The cells where the dashes do not show in the formula bar had the values typed without the dashes and are therefore in the cells as numbers and the dashes and leading zeros are being added by cell formatting. You will either need to convert all of the text values to niumbers, or all of the number values to text for AutoFilter to work.
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilter No Work (2000)

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •