Excel Tip - How to replace asterisk in Excel spreadsheet

In doing some analysis/cleansing of the urls and redirects for the launch of our new site, Candelamedical.com (check it out!), I was removing non-alphanumeric characters from the urls from the legacy site. 

Parentheses, commas, quotes, ampersands, no big deal. Find and replace all.  

The trouble came in when I went to remove the asterisks from the legacy urls. By this point I was humming along, I got aggressive. I didn’t do my find all check before executing the replace all. What came next are the makings of a Desk Jockey nightmare.

Excel started to process my request. Excel was doing exactly what I asked it to do…. The programming meaning of the asterisks. It was replacing all of the selected cells with the blank. 

Excel treated my asterisk in the programming sense instead of the literal way I was intending. In programming terms the “*” is a wildcard find anything that starts with this text or ends with that text and the opposite ending however indicated by the wildcard. 

During the ~5 minutes it took Excel to execute this task, I closed as many programs as I could to give it the processing power to execute the task (not sure if it is a real thing but it makes sense to me... :)) I also did a little research and found the error of my ways. A timely ctrl+Z reversed my error and Excel took an even more painstaking 5 minutes to process the request but my work was saved.

The trick of the trade here is to include a tilde in front of the asterisk. So in the find field you would put “~*” to get this done. Check out the source article for additional details.

You’re welcome! Happy jockeying!