Finding an Asterisk in Excel

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.

(Photo Tetsumo / CC BY 2.0)

---

Comments:

wsher - Dec 5, 2009

… 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)

---

madd0 - Dec 5, 2009

Actually, “Match entire cell contents” can remain checked, but of course, it will only find cells that contain _only_ an asterisk :)

---

Vinicius de Castro - Oct 3, 2012

Thanks a lot Mad =D (Your post is in English, the comment box could be also? Regards

---

madd0 - Oct 3, 2012

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!

---

John Black - Jun 1, 2013

Thanks

---

DN - Jul 3, 2013

Thanks for sharing this tip!

---