Table of Contents
Find and Replace with Wildcards in Excel is a useful tool that allows users to quickly and efficiently replace specific text or values within their Excel spreadsheets. Wildcards are symbols that represent a group of characters or values, making it easier to search for and replace multiple instances of a certain pattern or format. By using this feature, users can save time and effort by avoiding the need to manually search and replace each individual entry. This powerful tool can be particularly useful when dealing with large amounts of data or when making changes to a consistent pattern. With the ability to customize the search criteria using wildcards, users can easily find and replace text, numbers, or formatting in a single step, making data management and editing in Excel more efficient and effective.
Excel: Use Find and Replace with Wildcards
You can use the Find and Replace feature in Excel to find specific characters in cells and replace them with different characters.
One perk of the Find and Replace feature is that you can use the following wildcard characters to make specific replacements:
1. Replace All Characters Between Two Specific Characters
This particular example replaces the strings “this” and “that” along with all characters between these two strings.
2. Replace All Characters Before Specific Characters
This particular example replaces the string “this” along with all characters that come before this string in the cell.
3. Replace All Characters After Specific Characters
This particular example replaces the string “this” along with all characters that come after this string in the cell.
The following example shows how to use each method in practice.
Example 1: Replace All Characters Between Two Specific Characters
Suppose we have the following dataset that shows the name and position of various basketball players on some team:
Suppose we would like to replace all of the characters between the parentheses in each cell in the Position column.
To do so, highlight the cell range B2:B13.
Then type (*) in the Find what box and then leave the Replace with empty.
Once you click Replace All, the parentheses and all characters between them will be removed from each cell:
Example 2: Replace All Characters Before Specific Characters
Suppose we have the following dataset that shows the name and position of various basketball players on some team:
Suppose we would like to replace the string “ar” and all characters that come before this string in each cell in the Position column.
To do so, highlight the cell range B2:B13.
Then type Ctrl + H to bring up the Find and Replace window.
Then type *ar in the Find what box and then leave the Replace with empty.
Once you click Replace All, the “ar” and all characters before them will be removed from each cell:
Note: Each cell with a position of “Center” was unchanged since it didn’t contain the string “ar” anywhere in the cell.
Example 3: Replace All Characters After Specific Characters
Once again suppose we have the following dataset that shows the name and position of various basketball players on some team:
Suppose we would like to replace the string “ar” and all characters that come after this string in each cell in the Position column.
To do so, highlight the cell range B2:B13.
Then type Ctrl + H to bring up the Find and Replace window.
Then type ar* in the Find what box and then leave the Replace with empty.
Once you click Replace All, the “ar” and all characters after them will be removed from each cell:
Note: Each cell with a position of “Center” was unchanged since it didn’t contain the string “ar” anywhere in the cell.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: