Results 1 to 5 of 5
  1. #1
    drichard
    Guest

    Filtering Text/numbers

    A column that is a numeric/alphabetic fields. Formatted as text will not filter. It comes up blank. But if I select one of the exact numbers it works, but not with the Begin as or using wild characters. I tried the solution that was in the Microsoft Knowledge base (Article ID: Q170230) but it still doesn't work. Any suggestions? Attached is a sample of the file. Try to filter column "E" for Items beginning with 299.

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Text/numbers

    My understanding is that you can't do a "begins with" filter on numbers, as "begins with" is a string comparison. If you want to filter on all the numbers starting with 299, then I would suggest filtering on ">=290000 and <300000".

    Jon

  3. #3
    drichard
    Guest

    Re: Filtering Text/numbers

    That would be fine if it was all numeric but it some examples of the field is: 12345, 1298TC. I want everything that starts with 12. The <> numeric will not work in the TC instance. It doesn't pull them.

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Text/numbers

    Sorry, I didn't realise you were mixing your data. In that case, my only other suggestions are:
    1) along the lines of article q170230, except that I would have created the new data with the space at the end of the cell, rather than the beginning.
    2) create a new column and use the Text function with the number format "General" to convert everythin to strings.

    See how you go with those and let me know.

    Jon

  5. #5
    drichard
    Guest

    Re: Filtering Text/numbers

    Thanks for your help. I figured it out. Insert column, format column to GENERAL then put in the following formula in 1st cell of new column: =IF(T(##)="",FIXED(##,0,TRUE),##) where the ## is the cell reference of number need converted. Then drag down formula to last row, copy, paste special, values. Delete old column.

    For more details I wrote it up in the attached file.

Posting Permissions

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