Get wild with Excel
Question : I work with numbers that may or may not
end with asterisks. For example: 1234*
and 1234 denote two separate entities.
Let's say I have an Excel file of 10,000 entries
and just a handful of them end in an asterisk. Can
you come up with a solution to find those that
end in an asterisk by using Find? I don't have
much success, because Find treats * as a
wildcard. A search for * finds every single cell
that has a value.
Find and Find/Replace in Microsoft Excel
treat the asterisk and question mark characters as wildcards. An asterisk represents any
number of characters (including zero). Thus ca*t
matches cat, canoeist, and "caloric content." The
question mark represents exactly one character, so
ca?t matches cart but not cat or caret.
As you've noticed, trouble arises when you
search for one of the wildcard characters itself.
Fortunately there's a simple solution: Stick a tilde
(~) in front of the wildcard character. A search on ~* will match all cells that contain an asterisk. But
you're not quite out of the woods yet. Any formula
that involves multiplication will also match, since
the asterisk is the multiplication operator. To avoid
these unwanted "hits," click the Options>> button,
pull down the set of options titled Look in, and
change its value to Values (the default is
Of course, since the tilde character gets special
treatment as an identifier of wildcards, it's a kind of
wildcard itself. And, yes, in order to search for a tilde
you'll have to precede it with... another tilde.