Excel Logo

The Find and Replace tool is a powerful yet often forgotten feature of Excel. Let’s see how it can be used to find and replace text and numbers in a spreadsheet and also some of its advanced features.

Find and Replace Text and Numbers in Excel

When working with large spreadsheets, it is a common task to need to find a specific value. Fortunately, Find and Replace make this a simple task.

Select the column or range of cells you want to analyze or click any cell to search the entire worksheet. Click Home > Find & Select > Find or press the Ctrl+F keyboard shortcut.

Find & Select menu

Type the text or number you want to search for in the “Find What” text box.

The Find dialog box

Click “Find Next” to locate the first occurrence of the value in the search area; click “Find Next” again to find the second occurrence, and so on.

Next, select “Find All” to list all occurrences of the value including information, such as the book, sheet, and cell where it is located. Click on the item in the list to be taken to that cell.

Find all occurrences in the search area

Finding specific or all occurrences of a value in a spreadsheet is useful and can save hours of scrolling through.

If you want to change the occurrences of a value with something else, click the “Replace” tab. Type the text or number you want to use as a replacement value within the “Replace With” text box.

Replace text or numbers in Excel

Click “Replace” to change each occurrence one at a time or click “Replace All” to change all occurrences of that value in the selected range.

Explore the Advanced Options

Find and Replace has advanced features that many users are not aware of. Click the “Options” button to expand the window and see these.

Advanced Find and Replace options

One really useful setting is the ability to change from looking within the active worksheet to the workbook.

Click the “Within” list arrow to change this to Workbook.

Search the entire workbook for a value

Other useful options include the “Match Case” and “Match Entire Cell Contents” checkboxes.

Match case and entire cell contents options

These options can help narrow down your search criteria, ensuring you find and replace the correct occurrences of the values you’re looking for.

Change the Formatting of Values

You can also find and replace the formatting of values.

Select the range of cells you want to find and replace in or click any cell to search the entire active worksheet.

Click Home > Find & Select > Replace to open the Find and Replace dialog box.

Opening the Find and Replace dialog box

Select the “Options” button to expand the Find and Replace options.

Find and replace options

You do not need to enter text or numbers that you want to find and replace unless required.

Click the “Format” button next to the “Find What” and “Replace With” text boxes to set the formatting.

Set formatting to find and replace

Specify the formatting you want to find or replace.

Format Cells window to specify the formatting

A preview of the formatting is shown in the Find and Replace window.

Preview of the formatting

Continue with any other options you want to set and then click “Replace All” to change all occurrences of the formatting.

Using Wildcard Characters

When using Find and Replace, sometimes you might need to perform partial matches using wildcard characters.

There are two wildcard characters you can use in Find and Replace. The question mark and the asterisk. The question mark (?) is used to find a single character. For example, Al?n would find “Alan,” “Alen,” and “Alun.”

The asterisk (*) replaces any number of characters. For example, y* would find “yes,” “yeah,” “yesss,” and “yay.”

In this example, we have a list of names followed by an ID in column A of our spreadsheet. They follow this format: Alan Murray – 5367.

We want to replace all occurrences of the ID with nothing to remove them. This will leave us with just the names.

Click Home > Find & Select > Replace to open the Find and Replace dialog box.

Type ” – *” in the “Find What” text box (there are spaces before and after the hyphen). Leave the “Replace With” text box empty.

Using wildcards in Find and Replace

Click “Replace All” to modify your spreadsheet.

Alan Murray Alan Murray
Alan Murray has worked as an Excel trainer and consultant for twenty years. On most days, he can be found teaching Excel in a classroom or seminar. Alan gets a buzz from helping people improve their productivity and working lives with Excel.
Read Full Bio »

The above article may contain affiliate links, which help support How-To Geek.