Contextures

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.

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 next 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. Find All button

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

    Select all items in Find All list

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

    Delete Sheet Rows

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

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.

Video: Find and Replace With Wildcards

In this video, you'll see how to use the * wildcard with the Find and Replace commands. The * wildcard represents any number of characters, and you can use it anywhere in the Find string.

Download the Sample File

Download the sample Find and Replace workbook go to top

Excel Function Tutorials

SUM Function

VLOOKUP

INDEX / MATCH

COUNT Function

INDIRECT

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search Contextures

Search Contextures Sites

Search Contextures Sites

 

Excel Tools Add-in

 

 

Excel Data Entry Popup List

 

Last updated: July 13, 2017 7:51 PM
Contextures RSS Feed