How to Find and Replace Text and Numbers in Excel

Excel logo

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

Find and replace text and numbers in Excel

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

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

Search and select menu

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

The Find dialog

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

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

Find all occurrences in the search area

Finding specific occurrences or all values ​​in a spreadsheet is helpful and can save hours of scrolling time.

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 in the “Replace with” text box.

Replace text or numbers in Excel

Click “Replace” to change each occurrence one by one or click “Replace All” to change all occurrences of this value in the selected range.

Explore the advanced options

Find and Replace has advanced features that many users are unfamiliar with. Click on the “Options” button to enlarge the window and see them.

Advanced search and replace options

One really useful setting is the ability to switch from searching the active worksheet to the workbook.

Click the arrow in the “In” list to change this to a workbook.

Search the entire workbook for a value

Other useful options include the “Match case” and “Match all cell contents” check boxes.

Match case and whole cell content options

These options can help you narrow down your search criteria, making sure 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, or click any cell to find the entire active worksheet.

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

Open 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 search for and replace, unless required.

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

Define formatting to find and replace

Specify the formatting you want to find or replace.

Format Cells window to specify formatting

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

Formatting overview

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

Use of wildcards

When using Find and Replace, you may need to perform partial matches using wildcards.

There are two wildcards that you can use in Find and Replace. The question mark and the asterisk. The question mark (?) Is used to search for 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”, “yes” and “yes”.

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 delete them. It will just leave us with the names.

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

Type “- *” in the “Find What” text box (there are spaces before and after the dash). Leave the “Replace with” text box blank.

Using wildcards in Find and Replace

Click “Replace All” to modify your spreadsheet.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.