Special Characters are a Pain in the Asterisk

September 7th, 2009 by datapig Leave a reply »

There's an old saying that goes "You learn something new every day". That's fine, but it's embarrassing when what you learned that day is something that you should have learned years ago.

I recently was working with Access when I ran into a situation I've never run into before. As I did some research, it became clear that this "problem" I was having seemed to be common knowledge.

It revolved around special characters. Since we all have things to do today, I'll simplify the scenario to a basic example.

.

I was working with a table similar to this one, where there are number symbols (#) peppered throughout the values.

.

.

I decided to find all the values that have the number symbol just to see how many I was dealing with.

So I wrote this query. As you can see, I'm using wild card characters to tell access to return values that contain a number symbol .

..

.

This is what Access returned. You'll notice that Access didn't just return the values with number symbols as expected  - it returned even those values that don't have number symbols.

.

At this point, I'm thinking  "What the heck? Did I find a bug in Access?  Did I make a mistake in writing the query?  What's the answer?"

.

As it turns out, the answer was that I was having a brain fart.

You see, the number symbol is a special character used in Access queries to indicate that you want any number value.  So setting the criteria (LIKE *#*) will return any value that contains any number in it.  Of course I knew this, but I didn't put two and two together initially.

.

So after doing a little research, I learned that if you are querying for "special characters", you've got to wrap the offending character in Brackets ([]).  This query will return the correct results because the number symbol is wrapped in Brackets.

.

Obviously, the moral of this story is to avoid using special characters if you have a choice.

So what are the other "special characters" you have to watch out for?

Well according to Microsoft:

Space  
Apostrophe '
Quotation mark "
Apostrophe '
At sign @
Grave accent `
Number sign #
Percent %
Greater than sign >
Less than sign <
Exclamation mark !
Period .
Brackets [ ]
Asterisk *
Dollar sign $
Semicolon ;
Colon :
Question mark ?
Caret ^
Braces { }
Plus sign +
Hyphen -
Equal sign =
Tilde ~
Backslash \

.

Note:

In Excel, the only special characters that have caused me problems are the Asterisk (*) and the Question Mark (?).

These are the wildcard characters in Excel. They typically wreak havoc when trying to Find and Replace.

For example, If you try to find and replace all asterisks (*) with a hyphen as shown in this screenshot, you'll turn your entire spreadsheet into hyphens.

This is telling Excel to replace everything with a hyphen.

.

When you're trying to Find an asterisk or question mark, prefix it with a Tilde (~) symbol.  This way, you won't get fired.

Advertisement

5 comments

  1. Oakhome says:

    Thanks for that very handy tip on replacing asterisk with something else in Excel. Figuring this out has been on my ‘to-do’ list, but not done!

  2. Nick Burns says:

    How apropos! I was dealing with a spreadsheet yesterday where the user had marked columns with an asterisk (*) to indicate Yes. It took me awhile to figure out how to change the characters to something else (either a Y or an X) without changing the entire worksheet!

  3. Paul says:

    That’s funny… I remember learning that once before too. It’s obvoius that I have since forgot that little nuiance since I wind up doing iteratve gymnastics to work around those “special” characters… maybe I’m just special. :)

  4. melissa says:

    awesome. this made my day, my spreadsheet, and my job 10000 times better. you saved me hours worth of work. high five!

  5. Ben Kusmin says:

    The ampersand (&) also creates problems in Excel, particularly when you try to use one in a header or footer. The solution is to use two of the little buggers. Thus, if you want the header to say “Profit & Loss” you will type “Profit && Loss” into the Custom Header field:
    Huzzah!
    Ben Kusmin, a/k/a Excel Esquire

Leave a Reply