Excel tutorial: How to use search and replace functions

In this tutorial, you will learn how to use the Excel Search and Replace features to search for specific values in an entire workbook or in a single spreadsheet. You will also discover more advanced options to benefit the most of this feature and make it more efficient.

The search and Replace feature in Excel is crucial, especially when working on a large workbook. It can save you a lot of time, and that’s the most important thing these days. In general, we use this feature most often to search for one value and replace it with another without having an idea about the great possibilities it offers us. In this tutorial, we will show you how to search and replace data in your spreadsheet and share some tips on how to make your search more efficient.

Table of contents

How to use the Search feature in Excel

Search for cells with a specific format in Excel:

Search for a value in a range, spreadsheet or workbook.

How to use the replace option in Excel

Replace a value by another one

Search and replace the formatting in Excel

How to search and replace a break line in Excel

Use wildcard characters to search and replace

  How to use the Search feature in Excel

In this first section, we will approach the Search feature and see the steps to use it for different purposes. We will first see how to search cells with a specific format in Excel.

Search for cells with a specific format in Excel:

This is a very effective feature when you want to replace the existing formatting with another. For example, you can have cells with a blue background color and you want to change the background color of all these cells to yellow. The standard method of doing this is to select these cells and manually change their filling. However, this will take a lot of time. The Search and Replace function allows you to do this quickly and effortlessly.

Below are the steps to follow:

1. Select the range of cells in which you want to search for formatting. If you want to search for a specific format in the entire spreadsheet, select the entire spreadsheet.
2. Go to «Home » tab, and then in the « Edit » group, Click on « Search and select » and choose « Search » (keyboard shortcut: Ctrl + F).

3. In the « Search and replace » dialog box, click on « Options » button. This will enlarge the dialog box and show you more options :

1. Click the arrow next to the Format button. It will show a drop-down list with two options: « Format » and « Choose the format from the cell… ».

• You have the choice to manually specify the format you want to search by selecting the « Format » option, or select the format in a cell in the spreadsheet by selecting the second option.
• To select a format to search for in a cell on the sheet, select the « Choose format from cell » option, then click the cell in which you want to choose the format.

2. Once you have selected a format in a cell or manually specified it in the cell format dialog box, you will see it as a preview in the left of the format button:

3. Click on  « Next » or « Search all » :

Note:

Microsoft Excel usually saves the formatting options you specify. If you try to search for other data and Excel cannot find the values even if you have them in your data set, delete the formatting options from the previous search. To do this, go to the “Search and Replace” dialog box and click the « Options » button, then click the « Format » button and select « Clear Format Search ».

In the next part, we are going to see how to use the search function for a value in a range, spreadsheet or workbook.

  Search for a value in a range, spreadsheet or workbook.

Excel allows you to search for values (Text, Characters, Numbers, Dates, etc.) in a cell range, spreadsheet or in an entire workbook. Follow the steps below :

1. First select the range of cells in which you will search. In case you want to search the entire spreadsheet, simply select any cell in that spreadsheet.
2. Go to the « Home » tab and in the « Edit » group, click on the « Search and select » button and select the « Search » option from the appearing menu. Alternatively, you can simply use the « Ctrl +F » keyboard shortcut.  

3. The « Search and replace » dialog box will open. Enter the number , text or character to search in the « Search » box :

4.  [Optional], click the « Options » button if you want to access other research options :

• From the « In » list, select « Sheet » or « Workbook » if you want to search the current spreadsheet or the entire workbook .
• In the « Search » area , select « By row » if you want to search line by line from left to right starting with the active cell . Otherwise, select « Per column » to search column by column from top to bottom .  
• In the « Look in » box, select the type of data you want to search for: Formulas, values or comments .
• If your search is case sensitive, check the « Respect the case » box .
• Check the « Entire cell content» box to search for cells that contain only the search value .

5. Click  on « Search all » or  « Next » according to your wish :

• If you first click on  « Next », Excel will select the first occurrence of the search value. A second click will select the second occurrence and so on .
• However, if you click on « Search all » Excel will show you a list of all the occurrences it found in the selected area. So you can click on any entry to access it .  

Now that we know how to use the search value in excel we will move to another section in order to understand the use of the replace function in Excel .

How to use the replace option in Excel

Below are step-by-step instructions on how to use the « Replace » option to change or replace a value with another in a selected range, entire spreadsheet or even in a workbook .

  Replace a value by another one

Now that you’ve come this far, finding and replacing values in Excel will be a breeze. To replace one character, text or number with another, follow the steps below :

1. Select the cell range in which you want to replace text or numbers. If you want to replace characters throughout the spreadsheet, click any cell on the active spreadsheet .
2. Open the « Replace » tab in the « Search and replace » dialog box using the « Ctrl+ H » shortcut .
3. You can see that all the options are the same. You just have an additional field in the search window. You will notice that the « Replace by» box also has a formatting option . If you replace values , you can also choose to format the new cells in a specific way :

4. Now, all you need to do is enter the value you want to search in the « search » box, then enter by what you want to replace the search term in the « Replace by » box.

5. Click on « Replace » to browse and replace the results one by one, or on « Replace all » to replace all the values in the same time.

Search and replace the formatting in Excel

In the first part of this tutorial, we explained how to find cells with a specific formatting thanks to the « Search » tab in the « Search and replace » dialog box. The « replace» tab allows you to go further and modify the formatting of all the cells on the entire sheet or workbook.

1. Use the « Ctrl+H » shortcut to open the « Search and replace» dialog box.
2. Then, click on the « Options » button .
3. Remove all criteria from the « Search box », then click the arrow next to the « Format » button in the upper right corner .
4. Select « Choose the format from the cell », then click on the cell with the formatting you want to replace in the entire sheet .

5. You must now specify the desired format instead of the one selected in the previous step. Click the « Format » button arrow next to « Replace by » . You will be shown a drop-drown list with two options: « Format » and « Choose format from cell » . You can either specify it manually by choosing the « Format » option, or choose an existing layout in the spreadsheet by choosing the second option .
6. Once you have selected a format in a cell or manually specified it in the cell format dialog box, you will see it as a preview to the left of the format button.
7. Click the « Replace all » button.

You can use this technique to replace a lot of things in formatting. It can take and replace formats such as background color, borders, type, size ,font color , etc .

How to search and replace a break line in Excel

What do you do when you need to access a newline in an Excel cell? You Press « Alt + Enter ».

And what do you do when you want to cancel a return to a line? You delete it manually ... isn’t it?

Now imagine that you have hundreds of line breaks that you want to remove. In this case, manual removal will take you a long time. Fortunately, you do not need to do it manually. The Excel « Search and replace » feature will allow you to do this in a snap.

Below the steps to delete all the break lines at the same time:

1. Select the range of cells for which you want to remove line breaks.
2. Access the « Home » tab and click on the « Search and select» button then choose « Replace » (Alternatively, use the Ctrl+H shortcut).
3. To replace a break line with a space or any other separator, enter the line break character in the « Search » field by pressing the « Ctrl+J » shortcut. This shortcut is the ASCII code for the character 10 ( line break or line return) :

4. In the « Replace by » area, enter the replacement character for example a space character, then click on « Replace all ».

This would magically delete all the line breaks from your Excel spreadsheet:

Use wildcard characters to search and replace

Wildcard characters are useful in formulas, but you can also use them to facilitate your search and replacement actions.

Microsoft Excel’s « Search and replace » feature allows you to search for a particular text string in formulas or cell values . If you are unsure of a particular text string , you can use wildcards . Wildcard characters are used as placeholders when you cannot specify the exact text in a string .

You can use wildcards with partial text that you know to help you locate the string in the Excel cells:

• Use the asterisk when accepting a string of any length. For example, a search on « Wh*» will return « Who » or « What » .
• The question mark will return only values that have a single character in this space. For example , a search for « Wh*» won’t return « Whit » or « What » , but will only return « Who» .

If, for example, you have a large dataset with parentheses everywhere, deleting parentheses and text between them may take hours. However, the « Search and replace » Excel function can do it in less than 10 seconds using wildcard characters.

To use wildcard characters in your searches, follow the steps below :

1.  Select the data.
2. Go to « Home » -> « Search and select » and choose « Replace ». You can also use the Ctrl+H keyboard shortcut.
3. In the « Search and replace » dialog box, enter the search term in the « Search » field. In this example , we want to remove all parentheses and their text , thus , we used an asterisk , which is a generic character that represents any number of characters : (*)
4. Leave the « Replace by » field blank.

5. Click on « Replace all » to automatically search and replace all the matches. You can also click on « Next » to replace or locate the next match found .

If you want to search for a string containing a wildcard character, you can tell Excel not to treat an asterisk or a question mark as a wildcard character by having it preceded by a tilde « ~ ». For example, to search for cells containing questions marks, use « ~?» as a search criteria.

Conclusion :

Using the search and override function in Excel is very similar to its use in other Microsoft Office applications. But it comes with extra power that you can use to refine your search or make changes to your replacements. Once you’ve mastered this, you can make large-scale changes to your spreadsheets much faster.