Search Contextures Sites
Use the Find and Replace commands to locate specific text in a worksheet or workbook, or replace specific text with other text, or no text. These examples show how to use the commands, and what you can do with the selected cells, after finding them.
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:
- 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.)
- To open the Find dialog box, press Ctrl + F (or click the Ribbon's Home tab, then click Find & Select, and click Find)
- 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
- 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
- To select all the items in the list, press Ctrl + A -- all the listed cells will also be selected on the worksheetselected on the worksheet
- Close the Find dialog box
- With the cells still selected, click the Home tab on the Ribbon, and click the arrow for the Delete commands
- Click Delete Sheet Rowsselected on the worksheet
- All the rows for the selected cells are deleted, without a confirmation message.
- NOTE: you could Undo, immediately after deleting the rows, if you change your mind about deleting them.
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.
Download the sample Find and Replace workbook
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 Sites
Excel Function Tutorials
- Functions List
- 30 Functions in 30 Days
- COUNT / COUNTIF
- INDEX / MATCH
- MIN / MAX
- SHEET and SHEETS
- SUM / SUMIF
Contextures Inc., Copyright ©2016
All rights reserved.
Last updated: April 6, 2016 10:16 AM