As Seen on TechCrunch: Finofo Raises $1.65M CAD!

Excel Guide

Find and Replace in Excel: A Step-by-Step Tutorial

Whether managing a substantial dataset or a smaller one, there may be occasions when altering values in Excel becomes necessary. Be it rectifying misspelled words or interchanging values, Excel simplifies the process of locating and replacing values with just a few clicks.

Uncovering and Substituting Values in Excel

To locate and replace values in Excel, adhere to the following steps:

  1. Open your Excel workbook.
  2. Navigate to the Data tab.
  3. Click on Find & Select in the Editing group.
  4. From the ensuing drop-down menu, select Replace.
  5. Enter the desired value in the Find what: field.
  6. Input the replacement value in the Replace with: field.
  7. Click Replace All. Every instance of the value specified in the Find what: field will be replaced with the value entered in the Replace with: field.

Locating and Substituting Values within a Designated Range in Excel

For those instances when the focus is solely on a specific range of cells, the following steps can be followed:

  1. Open your Excel workbook.
  2. Select the range of cells where you want to locate and replace values.
  3. Navigate to the Data tab.
  4. Click on Find & Select in the Editing group.
  5. From the ensuing drop-down menu, select Replace.
  6. Enter the desired value in the Find what: field.
  7. Input the replacement value in the Replace with: field.
  8. Click Replace All. Every instance of the value specified in the Find what: field will be replaced with the value entered in the Replace with: field.

Utilizing Wildcards in Excel Find and Replace

For finding and replacing values containing specific characters or patterns, wildcards prove invaluable. These special characters can serve as placeholders for unknown characters within a text string. Here's how to utilize wildcards:

  1. Open your Excel workbook.
  2. Navigate to the Data tab.
  3. Click Find & Select in the Editing group.
  4. From the ensuing drop-down menu, select Replace.
  5. Enter the desired value in the Find what: field, incorporating wildcards as necessary.
  6. Input the replacement value in the Replace with: field.
  7. Click Replace All. Every instance of the value specified in the Find what: field will be replaced with the value entered in the Replace with: field.

Harnessing the ? Wildcard in Excel Find and Replace

The ? wildcard signifies any single character. For instance, to locate all cells containing the word "text" followed by any single character, employ the following search string:

text?

This will locate cells containing values like:

  • text
  • texts
  • text1
  • textA

Employing the * Wildcard in Excel Find and Replace

The * wildcard represents any sequence of characters. For instance, to locate all cells containing the word "text" followed by any sequence of characters, use the following search string:

text*

This will locate cells containing values like:

  • text
  • texts
  • text1
  • textA
  • text123
  • textabc

Applying Regular Expressions in Excel Find and Replace

Regular expressions offer a potent method for locating and replacing values in Excel. These patterns can match various text strings. To utilize regular expressions in Excel, the REGEXMATCH function is required. This function returns TRUE if the specified pattern is found in the given text string and FALSE if it is not found.

For instance, to locate all email addresses ending in ".com," use the regular expression:

.+\.com$

This will match email addresses like:

But not addresses like:

Using the ^ Wildcard in Excel Find and Replace

The ^ wildcard represents the beginning of a text string. For example, to locate all cells containing the word "text" at the start of the cell, employ the following search string:

^text

This will locate cells containing values like:

  • text
  • texts
  • text1
  • textA

Employing the $ Wildcard in Excel Find and Replace

The $ wildcard signifies the end of a text string. For example, to locate all cells containing the word "text" at the end of the cell, use the following search string:

text$

This will locate cells containing values like:

  • text
  • texts
  • text1
  • textA