Find and Replace

I am sure that as an Excel user you must have had many occasions when you had to check whether some text, number or format is available in a sheet or the workbook. Sometimes you might have to replace all instances of existing text with new text. Excel’s Find and Replace feature is available to specifically facilitate this. In this post, we will see some details about this feature and few cool tricks .

What is?

This is a feature in Excel to facilitate finding of cells with specific text, formula, format or comment etc. It is also possible to select any, few or all of such cells. Additionally, you can replace any text in the cell with any other text you want.

How to access?

Excel 2007 or later: Home > Editing Group > Find & Select > Find

Excel 2003 or earlier: Edit > Find

In all the versions, key board shortcut Ctrl+F shows up the same dialogue box.

Let’s see in details various options available in this feature.

Find Tab

This tab can be used when you only need to find text or numbers.

Find what box – You can enter text or numbers that you want to search for (this box is also a dropdown list that contains all the recently searched items, so you can even select from this list)

Options >> - You can further refine / expand your search by clicking on this button which on clicking shows few more options to work with. You will notice that Find what box size is reduced and few more options are available.

               

Clicking Format… shows up another dialogue box (similar to Format Cells box but here it is named as Find Format. You can define the criteria based on number style, alignment, font, border, cell colour or protection etc. Upon clicking on OK in this box, Formats you selected will be shown in Find and Replace box to the left of Format button.

Format… button also has a dropdown button at the end which has few more options.

Format… in the dropdown menu will show the Find Format dialogue box

Choose Format From Cell… will let you select a cell so that it’s format will be considered for search

Clear Find Format lets you clear off any format you already selected

Within box: To search for data in a worksheet select Sheet or select  Workbook to search in entire workbook

Search box: To search for data in rows or columns, click By Rows or By Columns.

Look in box: Select appropriate option to search for data in Formulas, Values, or Comments.

Match case check box: To search for case-sensitive data, select it

Match entire cell contents check box: To search for cells that contain just the characters that you typed in the Find what box, select this.

Find All: Lists down all the occurrences of text / format entered in Find what box.

Find Next: Find and selects the cell that contains the next occurrence of text / format entered in Find what box.

Close: Closes the Find and Replace box without any further action.

Replace Tab

This tab can be used when you need to replace text or numbers. All features discussed above will stay with following additional boxes.

Replace with: This box is similar to Find what, and you need to define what you want to replace with.

Format button: See above.

Replace All: Replaces all the occurrence of text / format in Find what with text / format in Replace with.

Replace: Replaces the first occurrence of text / format in Find what with text / format in Replace with. This selects next available occurrence.

That’s the theory. Now let’s get into the cool part to see few practical uses of this.

Tip 1: Find in Only Particular Region

Select the region you want before accessing Find and Replace feature. If more than one cell is selected, Find and Replace checks only in the selected part. Select any one cell if you want Excel to find in entire sheet.

Tip 2: Select All Occurrences

Find and Replace feature can list down all the occurrences of text, number or format you are looking for. However, sometimes you might want to select all the cells that contain a specific format so that you can change that format for all the cells in one shot. You can use Find and Replace to do this for you. Once you selected the format you are looking for, click Find All and Excel will list all the cells in the dialogue box. Press Ctrl+A to select all of referenced cells in the list. Click Close. Now, your cells are selected.

You can also keep pressing Ctrl and click individual entries with mouse to select only few cells of your choice.

Tip 3: Use Wildcards

Find and Replace supports entry of wildcards. You can use ? (question mark) to search for a single character and * (asterisk) for any number of characters. For example: if you type b?ll in Find what then Excel will search for all words that has b in the start and ending with ll like bill, ball, bell etc. This feature is of particular use if you know the number of characters & any character of what you are looking for.

Using asterisk will give you more flexibility. Example: if you search for b*t, Excel will look for all the words that has b in the start and t in the end like bat, best, bolt, bright etc.

Tip 4: Search for Asterisk or Question Mark

In our earlier tip, we saw that you can use ? and * as wildcards. But what if you want to search for them specifically? Every time you use these characters in Find what box, Excel will think you are using them as wildcards. In this case, you should place a ~ (tilde) before ? or *. This will tell Excel that any character that follows tilde will have to be looked for as it is.

For example, if you are searching for a question mark, you will enter ~? in Find what box.

Happy Learning!!