Filter Asterisk Character ("*")


Hello friends I hope you are keeping track of our Excel tips and learning new things everyday. Now it's time to get ready for another Excel tips. Today's post will be short and but very unique. Today we are going to learn, how to filter the asterisk character ("*"). Suppose you have a data which contains few asterisk character ("*") and you want to filter rows containing asterisk character ("*").

Now if you have been new to this situation you will simply say it's very easy. But mind it, It's not! There is a special technique to this and I'll take you step to step through it.

1. Apply filter to your complete data which contains asterisk ("*")
2. Click on filter drop down, Select Text Filters and than Contains...

3. It will open Custom AutoFilter pop-up box, here against the drop down with contains is another blank drop down. In this use Tilde ("~") before Asterisk ("*") and hit OK.

With this I wrap today's post, practise it and share it with everyone! "Happy Learning"

15 comments:

  1. Thanks for that tip...it really helps.

    ReplyDelete
  2. thank you so much Mr. Prakash

    ReplyDelete
  3. This helped me.

    ReplyDelete
  4. Thank you, thank you, thank you!

    ReplyDelete
  5. what if there's two asterisks. as in:

    * hotel A
    * hotel B
    * hotel C
    ** alphabet hotels
    * hotel 1
    * hotel 2
    * hotel 3
    ** numeric hotels

    ReplyDelete
    Replies
    1. Tough ask Joko...

      The above method won't work in this situation.

      Suggesting work around...

      For all * in your data replace it with any sign (I am replacing it with $)

      Ctrl + H > In Find what field put (~*) and in Replace with field put ($) that will turn your data as follows

      $ hotel A
      $ hotel B
      $ hotel C
      $$ alphabet hotels
      $ hotel 1
      $ hotel 2
      $ hotel 3
      $$ numeric hotels

      Now you can filter the data with $$ or multiple $$$ as criterion.

      Hope it solves the problem ;)

      Delete
  6. Or you can use ~* for each *
    For one asterisk filtering use ~*
    For two asterisk filtering use ~*~*
    Tilde is escape character. For each sequence of * you can use ~*

    ReplyDelete
  7. that is awesome. thanks a lot for rare find tricks.

    ReplyDelete
  8. Awesome article…..truly appreciated. We have an amazing tool, You can try this out Find and Replace in Excel tool which help you to find and replace multiple words in multiples excel files . Thanks

    ReplyDelete
  9. Came across this during a search - very helpful thanks!

    ReplyDelete