Filter values with question mark or asterisk in Excel

Zdeněk Nešpor Excel Leave a Comment

Asterisk * and question mark ? play special roles in Excel. Both are wildcard symbols. Asterisk can replace any string. A question mark can replace any single character.

If you are working with SEO datasets, you can and probably will come across URLs containing these special wildcard symbols, for example, URLs with UTM or other parameters. Luckily the solution is pretty straightforward.

Wildcards in Excel

An asterisk in the filter is used to match any string of any length. A question mark is used for matching a single character at a specific position. Check the examples below explaining how the matching works.

SOURCE DATAexample*

*.orgexample.c?mexample.c??
example.comexample.comexample.comexample.com
example.orgexample.orgexample.org
example.eduexample.edu
example.co.ukexample.co.uk
example.camexample.camexample.camexample.cam
example.carexample.carexample.car

How to to escape ? or *

You have to escape special wildcard characters by putting tilde ~ in front of them. It will neutralize wildcard effect and * or ? will be interpreted as a standard character. And that is it.

How to type ~ tilde

Finding and typing tilde can be another challenge. It’s not a very common character. You can usually find it in the upper left corner of your keyboard. Methods on how to type tilde are listed below. Or you can just simply copy it right here ~ and paste into Excel.

  • Ctrl + Alt + 1 (number 1 key in the alphanumerical area of your keyboard right under F1-F12)
  • Right Alt + 1 (number 1 key in the alphanumerical area of your keyboard right under F1-F12
  • Alt + 126 (numbers 1, 2 and 6 in the numerical area on the right of your keyboard, hold Alt and press numbers in stated order)

Never heard about tilde before and wanna know more? Try reading the Wiki entry: https://en.wikipedia.org/wiki/Tilde.

Conclusion

This solution is super easy to do but hard to find since Excel doesn’t give any hints in its interface. Enjoy and have fun with filtering non-standard URLs. Rare, unusual cases are the best. It gives us reason to learn new techniques and methods.

READ. SHARE. REPEAT.
  •  
  •  
  •  
  •  
  •  
  •  

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.