Excel Find and Replace with Wildcard

With the Find and Replace feature in Excel, you can look for specific characters, and replace them with different characters, or replace them with nothing. You can also use wildcard characters in the Find What box, to make the find feature more flexible. The short video below shows you the steps.

Video: Use Wildcard in Find and Replace

In this video, you’ll see how to use the * wildcard with the Find and Replace commands, to clean up a list of names.

Warning: Remember to make a backup copy of your original data, before you try this technique.

For the written steps, and the Excel sample file, go to the Excel Find and Replace page, on my Contextures site.

Video Timeline

00:00 List of Names to Clean Up
01:02 Make a Backup of Data
01:30 Remove Colons and Text
02:30 Remove Hyphens and Text
03:00 Remove OF and Text

Note: There is a full transcript below the video.

Get the Sample File

For the written steps, and to download the Excel sample file, go to the Excel Find and Replace page, on my Contextures site.

The sample file is in xlsx format, and does not contain any macros.
______________________

Video Transcript: Use Wildcard in Find and Replace

I’ve been given a list of names to clean up in Excel, and this is just a small sample of a long list.

These are all fake names, but we’ll see how you can remove text, either before or after a specific character or string of characters.

In this column, we want to get rid of anything that’s after a colon. So here’s the name of a law firm, a colon, and then the name of an individual there. So we want to remove the colon and anything after that. And there’s another example here.
There are also a few that have a hyphen. Again, we want to remove the hyphen and anything after it. We also want to remove the space before the hyphen.
And the third thing we want to do is find any that have the word “of”, remove that word, the space after it, and anything before that. So we have law office of, law offices of, and the law office of. We also have someone with OF in their name, and we want to make sure that doesn’t get affected.

Make a Backup of Data

Before I make any changes, I’m going to make a copy of this data. I don’t want to work on the original. I want to keep that safe in case I have to go back to it. So either:

make a copy of the whole workbook
OR, at least make a copy of the names you’re going to work on.

So I’ll select these two columns and copy them. Then we’ll go to Sheet2, click here and paste.
So now I have a copy that I can work on, without any concerns about the original data.

Remove Colons and Text

The first thing I’m going to do is try and find the colon and delete anything after that.

I’ll select the column where I want the changes, and I’m going to use the Find and Replace window

the quickest way to get to that is ~~Ctrl + H~~
and here’s the Find and Replace window.

I’m going to be looking for a colon. So I’ll type that, then I want anything after that.
And the way we represent anything is with a wild card, which is Shift + 8.

So the asterisk represents any characters or no characters.
So if it ended with a colon, it would also find that. So here, we’re going to find colon John Wilson.
It should also find colon Alice Smith.

We want to replace that with nothing. So I’m going to leave this blank.
I will click Replace All, and it should replace the two items that I can see here.

It’s telling me it made two replacements, so that looks correct.

Remove Hyphens and Text

The next thing we’re going to change is a hyphen and anything after that.

So in this case, we want to include the space before the hyphen, and the hyphen and anything that follows.

So I’m going to delete what’s in the find box.
I’ll type a space character, hyphen, and then the wildcard, again, Shift + 8.
I want to replace that with nothing.

So I can see two records here that have hyphens. Click Replace All, and it made two replacements.

Remove OF and Text

The final thing I’m looking for is the word of, so I wanted to find the word of which is on its own, not as part of another word.

So I’ll, again, clear out this Find What. This time I want it to replace whatever is before that.
I’ll start with the wild card, Shift + 8 and space O F space.
Replace that with nothing. So it should find one, two, three, and ignore this one.
Click Replace All made three replacements and it left the name that has OF untouched.

So you can use, Find and Replace with wildcards to either remove text before or after a specific character or string of characters.

______________________