Wild cards and regular expressions

If I’m going to discuss finding and replacing, I really need to also discuss wild cards. Most people are probably familiar with the concept of wild cards, in one form or another. I assume the term comes from card games, where jokers can be used to represent any other card that’s needed. People may also be familiar with the concept, if not the term, from Scrabble, where there are blank tiles that can represent any other letter tile.

Wild cards in spreadsheets (and in word processors, and in internet searching, etc.) are characters that can represent other characters – often more than just one. That is to say, if * is a wildcard then educat* could be educate, education, educating, educator, educatxyz, and so on.

Regular expressions are less commonly known. Basically, they’re wild cards with more precision. Instead of just any character you can specify just numbers or just letters. You can even specify that it’s at the beginning or end. The more you know the more powerful regular expressions get, but it’s possible to do basic stuff with basically the same amount of effort as you’d use with regular wildcards. I mention this because Excel uses basic wildcards, but Google Docs Spreadsheet and Open Office Calc both use regular expressions instead. If you want to know more than the basics, though, I found a couple of good guides here and here.

In both Google and Open Office, regular expressions are optional. If you try to search for a regular expression and it doesn’t work, check to make sure they’re turned on.
– In Google Docs you have to open the full search menu (not just the mini one you get with ctrl-F), and mark the box next to “Search using regular expressions”.
– In Open Office Calc you need to open the More Options section of the Find & Replace screen, and then mark the box next to “Regular expressions”.

That said, here’s a quick table showing the basic options.

To search for Excel Google &
Open Office
# characters 1 ? .
0 or more * .*
1 or more ?* .{1,}
1, 2, or 3 n/a .{1,3}
Exactly 3 ??? .{3}
Types 1 number n/a [0-9]
1 letter n/a [a-zA-Z]
Note: Excel must
have “Match entire
cell contents”
“a” at start a* ^a
“b” at end *b b$
“a” at start and
“b” at end
a*b ^a.*b$

Looking through that table, you might be wondering, “so how do I search for an asterisk (a *)?”. Well, asterisks and other special characters (?* in Excel, .?*[]{}^$ in regular expressions) can be searched for by preceeding them with an escape character. Excel’s escape character is a tilde (~), and in regular expressions it’s a backslash (). And, in case you’ve jumped to the next obvious question (How do I search for an escape character?), the answer is that you put two of them, one in front of the other.

To search for Excel Google &
Open Office
Special characters * ~* *
? ~? ?
~ ~~ ~