Home > Skills > Data Entry > Find and Replace

Excel Find and Replace

Examples show how to use the Find and Replace commands to find text in a worksheet or workbook, or replace text with other text, or no text.

Also see the FIND function page - formulas to find text (case-sensitive)

Video: Find All and Delete Rows

In this video, you'll see how to select several rows that contain similar data, and delete them all at the same time, with the Find All and Delete Sheet Rows commands. There are written steps below the video

Find All and Delete Rows

There is a Find All button in the Find dialog box, that lets you select several cells that contain similar data. Once the cells are selected, you can delete them all at the same time, with the Delete Sheet Rows commands. In this example, we'll delete all the rows that contain the word "paper" in column B.

Follow these steps to find and delete, or watch the video in the previous section:

1. Select column B, so the Find command will be limited to that column. (NOTE: You could select columns B and C, to find paper in either of those columns. Or, select a single cell on the sheet, to find paper anywhere on the sheet.)
2. To open the Find dialog box, press Ctrl + F (or click the Ribbon's Home tab, then click Find & Select, and click Find)
3. In the Find box, type the word paper -- we are not concerned about upper or lower case, so the word can be typed in all lower case
4. Click the Find All button, and a list of cells appears, below the Find All button, with the first item selected. That cell is selected on the worksheet



5. To select all the items in the list, press Ctrl + A -- all the listed cells will also be selected on the worksheet
selected on the worksheet



6. Close the Find dialog box
7. With the cells still selected, click the Home tab on the Ribbon, and click the arrow for the Delete commands
8. Click Delete Sheet Rows
selected on the worksheet



9. All the rows for the selected cells are deleted, without a confirmation message.
10. NOTE: you could Undo, immediately after deleting the rows, if you change your mind about deleting them.

Video: Find and Replace With Wildcards

In this video, you'll see how to use the * wildcard with the Find and Replace commands, to clean up the names in a contact list. There are written steps, and the full transcript below the video.

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

List of Names to Clean Up

In this example, there is a contact list in Excel, and we need to clean up a column of text, by doing the following:

1. Remove all text AFTER a colon “:”, including the colon
2. Remove all text BEFORE any instance of “of” E.g., “Law Offices of ”
3. Remove all text AFTER a dash (hyphen) “-”, including the dash

Find and Replace With Wildcards

To clean up the list, I'll use the Find and Replace feature in Excel.

I'll do the cleanup in 3 steps, using an asterisk (*) wildcard in the Find string.

• The * wildcard represents any number of characters, including zero characters
• You can use the wildcard anywhere in the Find string
• You cannot use the wildcard in the Replace string

1) Find and Replace Colons and Text After

First, I want to remove any colon, and all the characters after the colon.

• Select column A
• To open the Find and Replace window, press the keyboard shortcut Ctrl + H
• In the Find What box, type a colon and an asterisk:       :*
• Leave the Replace With box empty, because you want to remove the characters, and not replace them with anything
• Click Replace All

2) Find and Replace Hyphens, Spaces and Text

Next, I want to remove any to remove hyphens, and any text after them. I want to remove the space before the hypen too, so include that in the Find box.

• In the Find What box, type a space and hyphen, followed by an asterisk: [space]-*
• Leave the Replace With box empty
• Click Replace All

3) Find and Replace "of" and Text Before

Finally, I followed the same steps to remove the word “of”, and any text before it.

We don't want to remove names that contain "of", so be sure to include space characters before and after the “of”

• In the Find What box, type an asterisk, followed by a space, “of” and another space :
  • * of
• Leave the Replace With box empty
• Click Replace All

Cleaned Up List of Names

After those 3 simple steps of finding and replacing, the text is cleaned up, and you can see the results in the screen shot below..

NOTE: If you need 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

Video Transcript: Find & Replace With Wildcards

Return to video

Here is the full transcript for the video: Find and Replace with Wildcards

List of Names to Clean Up

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 and 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.

Return to video

Get the Sample File

Get the sample Find and Replace workbook

Excel Function Tutorials

FIND Function

SEARCH Function

SUBSTITUTE or REPLACE

VLOOKUP

INDEX / MATCH

COUNT Function

INDIRECT