We help IT Professionals succeed at work.

Can filtering on data with a ~ in Excel be a bug, or is it something else?

Heyitsjoe80
Heyitsjoe80 used Ask the Experts™
on
We have a user that gets data generated by a server.  The data happens to have ~ (Tilde's) in it.  When the user tries to filter on this data in Excel, the data does not show up.  Here's the repro example he's giving us.
1. Create new Excel workbook
2. In A1, type New Header
3. In A2, put in A ~ ?
4. In A3, put in B ?
Excel Repro setup5. Turn on Filtering, and drop  down filter selection box
6. Select A ~ ? ONLY, and click OK.
FilterSetupIf repro'd right, nothings shows up.  However, if both are selected, or just B ? is selected, it's filtered properly.

Is this a bug?  Or is there something going on with the ~ because it's an Escape Character?  The user insists that the data cannot be changed.  I've tried to give him some workarounds, but he insists it's a bug and we need to get Microsoft involved.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
I cannot reproduce your problem in either WIndows Excel 2010, Excel 2013 or Excel 2016/Office 365. I have the Office Insider Fast version, so if the bug has been corrected, I'll have the fix.

Which version of Excel does your user have?
Kesavan JeganarayananIT Consultant

Commented:
Microsoft Excel uses the tilde (~) as a marker to indicate that the next character is a literal.
Cant use it in the filter.

Author

Commented:
I am able to reproduce it on O365 Semi-Annual(Targeted) 1902.  I'll change over to Monthly and see if I can reproduce it, Byundt.  I made a quick video repro'ing the issue: https://youtu.be/BCCUfgf9noM

Kesavan - that's what I have been reading and reported to the user, he's not buying it however.  Do you have any solid information saying this?  Everything I find just says it's an Escape wildcard and goes on how to use it.  Is there something that says, if you try to filter on data with a tilde and a space after it, it will fail?
IT Consultant
Commented:
Wildcards in Excel:
Asterisk (*), Question Mark (?),and Tilde (~) when use them in the filter, it may not work.
Here is the link for reference:
https://spreadsheeto.com/wildcard/