One of the benefits to having an Excel website is the interesting questions that I get in my email. I can’t help with every request, but some lead to blog posts or articles on my Contextures website.
Last week, someone asked how to clean up a column of text, by doing the following:
- Remove all text AFTER a colon “:”, including the colon
- Remove all text BEFORE any instance of “of” E.g., “Law Offices of”
- Remove all text AFTER a dash “-”, including the dash?
What would you do? My first thought was Text to Columns – the text strings could be split at a colon or dash. It wouldn’t help with the “of” problem though, without a few extra steps.
Use a Wildcard
After thinking about this for a couple of minutes, I realized that Find and Replace would do a much better job than Text to Columns. You can use an asterisk (*) wildcard in the Find string, and that makes it very flexible.
The * wildcard represents any number of characters, and you can use it anywhere in the Find string.
First, I want to remove any colon, and all the characters after the colon.
- Select column A, and use the keyboard shortcut Ctrl + H to open the Find and Replace window.
- In the Find box, type a colon and an asterisk (Shift + 8): :*
- Leave the Replace With box empty, because you just want to remove the characters, not replace them with anything
- Click Replace All
Use Spaces With the Wildcard
Follow the same steps to remove hyphens, and any text after them. We want to remove the space too, so include that.
- In the Find box, type a space and hyphen, followed by an asterisk: [space]-*
Follow the same steps to remove the word “of”, and any text before it. Be sure to include space characters before and after the “of”.
- In the Find box, type an asterisk, followed by a space, “of” and another space : *[space]of[space]
The Cleaned Up Text
After a few seconds of finding and replacing, the text is cleaned up. If you have to do this type of cleanup frequently, you could create a macro to do the steps. However, for a one-off job, this is a quick way to clean things up.
For other ways to work with text, you can visit the TEXT function page on my website.
Watch the Video
To see the steps for using a wildcard with Find and Replace, please watch this short video.