Excel Find and Replace using Wildcards

I find Excel to be one of the most versatile and useful apps you can have in your toolbox. Recently, I had a large list of names and email addresses that I needed to parse out, and I used a cool feature in Excel to accomplish this.

Imagine that you have a large list that contains both people’s names and their email addresses in a single cell in the following format:

John Smith <JohnSmith@toplineresults.com>
Mary Diroma <MaryDiroma@contoso.com>
Doreen Xijang <DoreenXijang@contoso.com>
Thomas Klip <ThomasKlip@adventureworks.com>

I needed a way to extract only the person’s name from each of these rows and discard the rest. There are many ways to do this in Excel, but there is one tool that worked exceptionally well for me at that moment. This is the wildcard find and replace feature.

First, highlight the column or cells on which you want to perform this operation.

Next, press CTRL-H to open the Find and Replace window.

In the “Find what:” box, type “ <*>”.

Leave the “Replace with:” box blank.

Press the “Replace All” button and your cells will only contain the names of the individuals.

The reason this worked is that Excel searched for “<” and “>” and the wildcard indicated that any character could be in between those two brackets. The “Find” part of this process found the brackets and email addresses inside the brackets and replaced them with nothing.

The wildcard find can also be used for more. Using the same original list, if you wanted to get only the domain names of the email addresses, you could type “*@” into the find box and again replace it with nothing.

You could then do a find and replace on the “>” character to remove the “>” at the end to complete the process.

You could also retrieve the full email address by using the find string “*<”

And again using find and replace “>” to get rid of the “>” at the end.

Excel is a must-have in every consultant/developer’s toolbox. TopLine Results specializes in data manipulation in Excel as part of its day-to-day operations working with customer relationship management (CRM) and digital marketing automation systems. If you need help with data manipulation, let us know by calling 1-800-880-1960 or email info@toplineresults.com.