I was sitting at my desk when, suddenly, someone came in the office and asked the weirdest question:
We’re working on Excel and there’s a column with order number that end with a star and we’d like to remove it. Does anyone know how?
Obviously, we all immediately responded in choir:
Why, Ctrl+H (or Edit > Replace) and you replace character ‘*’ with nothing!
Except that, obviously, it didn’t work. The problem: the asterisk (‘*’) is a special character when you are trying to find something in Excel; it’s a wildcard or substitution character.
But there has to be a way to find an asterisk in an Excel cell, isn’t there?
Of course! Everything is possible with computers! All you have to do is “escape” the special character, i.e., precede it with another special character; in our case, it’s the tilde (‘~’).
Look for the string ‘~*’ (without the apostrophes, of course) and replace it with nothing at all; that will delete the asterisks from your Excel worksheet.
6 commentaires sur “Finding an Asterisk in Excel”
… works if the following box is UNchecked:
Match entire cell contents
(before i found this great tip, i had tried all the esacpe chars i knew about from yesteryear technologies, and had unproductively checked this box in my travels thinking that it might be a LITERAL INTERPRETATION cue)
Actually, « Match entire cell contents » can remain checked, but of course, it
will only find cells that contain _only_ an asterisk 🙂
Thanks a lot Mad =D
(Your post is in English, the comment box could be also?
I haven’t figured out how to configure Disqus to change languages depending on the post, but I agree, and I’d love to do so!
Thanks for sharing this tip!