Home > Skills > Data Entry > Find and Replace Excel Find and ReplaceExamples 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) |
Find All and Delete RowsThere 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:
|
Video: Find and Replace With WildcardsIn 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
|
List of Names to Clean UpIn this example, there is a contact list in Excel, and we need to clean up a column of text, by doing the following:
Find and Replace With WildcardsTo 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.
|
1) Find and Replace Colons and Text AfterFirst, I want to remove any colon, and all the characters after the colon.
2) Find and Replace Hyphens, Spaces and TextNext, 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.
|
3) Find and Replace "of" and Text BeforeFinally, 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”
Cleaned Up List of NamesAfter 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 WildcardsHere is the full transcript for the video: Find and Replace with Wildcards |
List of Names to Clean UpI'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 DataBefore 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 TextThe 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 TextThe 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 TextThe 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. |
Get the Sample File Get the sample
Find and Replace workbook Excel Function Tutorials
|
Last updated: January 16, 2023 7:40 PM