+3 votes
8 views
in Sell ​​on the Internet by (73.5k points)
reopened
Find and Replace in Excel: Easy Tutorial with Pictures

1 Answer

+4 votes
by (487k points)
edited
 
Best answer

image

Find and Replace in Excel: Easy Tutorial with Pictures

When it comes to tables, calculations or formulas, you usually work with Excel. The famous spreadsheet program is part of the Microsoft 365 package and is available for Windows and macOS operating systems. It is an ideal tool to clearly structure values , especially if they are numerous. However, when files include a lot of data, they can get a bit confusing. In this case, if you need to find certain combinations of letters or numbers and substitute them, you can use the find and replace functions in Excel, which allow you to do it with just one click of the mouse. Next, we explain them step by step..

Index
  1. Find and Replace in Excel step by step
    1. Search function - first step
    2. Search function: second step
    3. Search function: advanced options
    4. Replacement function - first step
    5. Replacement function: second step
    6. Replace function: advanced options
  2. Wildcard-characters: find and replace undefined content
    1. Question mark (?)
    2. Asterisk (*)
    3. Virgulilla (~)

Find and Replace in Excel step by step

Both functions are closely related . To run them, you can use the corresponding option in the menu or various keyboard shortcuts. The two functions are in the same window, although in different tabs. Follow these steps to access and use them:

Search function - first step

Do you have a large Excel table with many values ​​and you are looking for a specific combination of letters and numbers? The Search function is the ideal tool for this. To open it, select the? Home? Tab. from the menu, and then open the drop-down menu for? Find & Select ?. Press? Search? to open the window, where you will find all the options.

advice

You can open this function even faster using a keyboard shortcut: press? Ctrl? and? B? on the keyboard at the same time..

image
You can open the Search function window from the menu

Search function: second step

This opens the? Find and Replace? Window. in Excel. You will automatically find yourself in the "Search" tab. To find a specific combination of letters and numbers, enter it in the box? Search ? and then click the button? Find next ? or press the return key on your keyboard. Excel will highlight the first cell that contains the term. Every time you click on? Find next ?, the program will show you the next cell that includes it.

image
The Find and Replace window has two separate tabs for the two functions.

The function ? Search all ? it is especially useful in large Excel tables with many values. If you click on this option, Excel shows you all the cells in the table that contain the search term. If you choose one of the results by clicking with the mouse, the program will highlight the cell where it is located. Additionally, Excel offers the option to sort the results. To do this, click on the column title..

image
With the function? Find all ?, Excel displays all results in a table.

Search function: advanced options

Excel offers many other possibilities to refine your search. First of all, open the advanced options by clicking the "Options" button.

image
Excel offers several advanced options to further refine your search

In this way, Excel expands the window and offers several possibilities : if you open the drop-down menu next to? Search ?, you can set the range in which the term is searched. If you select? Sheet ?, Excel will only search the current worksheet, while if you choose? Book ?, the program will search all sheets. You also have the option to search by rows or columns, which does not affect the search results themselves, only the order. This setting only determines in which direction the search is performed, starting from the currently selected cell. Finally, Excel offers the option to search in specific ranges using? Search in ?, where you can choose between formulas, values ​​and comments.

Note

The formulas are not visible, because Excel only displays the results in the cell. For example, if a cell contains the Excel function SUM? = SUM (4 + 4) ?, the cell will only display? 8 ?, the result. With the function? Search in: Formulas ?, the program also finds the implicit number? 4 ?.

If upper and lower case letters must be taken into account, do not forget to check the corresponding box. When the option of? Match the contents of the whole cell? is checked, Excel only displays cells that contain only the search term.

image
Excel's advanced search options limit results

To search for cells with a specific format , click the button? Format ?. A new window will open in which you can set the format you are looking for. If you prefer, open the drop-down menu by clicking the arrow to the right of the button. Press? Choose cell format? and then click on a cell that has the desired format.

image
You can only search for cells that have a specific format.

Replacement function - first step

Do you have to correct any value in the table, but don't remember exactly where it is? Do you need to replace the content of a cell more than once? With the function? Replace? you can do it easily. In this case, Excel replaces one value with a different one, after automatically searching for the content.

If you have just performed a search, the window for the replacement function will already be open: just select the corresponding tab. It is also possible to use the shortcut from the? Home? Tab. from the menu, in which you must open the drop-down menu? Find and select? and press the option? Replace ?.

advice

To open the function? Replace? more quickly, you can use a keyboard shortcut : press the? Ctrl? and? L? at the same time.

image
From the menu you can directly access the? Replace ?.

Replacement function: second step

The find and replace functions in Excel have a very similar structure. To substitute a value, first enter the contents of the cell in the? Box. Search ?. If you leave it like that, the program will only look for those values ​​in the table. To do this, proceed as we explained in the previous sections. Instead, to replace the combination of letters and numbers you are looking for with a different one, enter it in the? Box. Replace with ?.

There are two ways to find and replace in Excel: if you select? Replace ?, you will modify the content of the cells one after another. This ensures that you have a better overview , especially for large documents with many cells.

image
You can replace the content cell by cell or all at once with a single click.

If you are sure that you want to replace the content that you have indicated in all cells, click the button? Replace all ?. A dialog box will automatically open showing you how many cells have been modified.

advice

If you want to remove the search term , simply leave the line? Replace with ?.

image
When all the content has been replaced, Excel shows you how many items have been changed.

Replace function: advanced options

As with the search function, Excel also offers advanced settings for replacing table values, which are largely identical to the search options. Just the option? Search in? It differs a bit as it is limited to formulas and does not include the option to choose values ​​or comments.

image
The advanced options for the replace function are almost identical to those for the search.
advice

If several people work on the same table, it is easy for a value to be entered twice by mistake. Excel also offers a handy function to solve this problem, which allows you to eliminate duplicate values. We explain how to use it in our article on how to remove duplicate values ​​in Excel.

Wildcard-characters: find and replace undefined content

In Excel documents, especially longer ones, you may want to search for an undefined term . To do this, Excel gives you the possibility of using wildcard characters . In this case, the program searches for all letter combinations that meet the indicated requirements. Here are the only wildcard characters that Excel recognizes:

Question mark (?)

The question mark represents a single undefined character . For example, if you search for? N? Nca ?, Excel will show you results like? Never ?.

Asterisk (*)

The asterisk has a similar function to the question mark. The difference is that it does not represent a single character, but any number of characters : for example, if you enter? N * a ?, Excel will find? Payroll? or never?. If you need to find all the words that begin with a letter, such as S, you must enter? S * ?.

Virgulilla (~)

You wonder how to find these same characters when we don't want to use them as wildcards. In this case, the tilde is used, which you must enter just before the corresponding character: for example, you can search for? ~ ?? or? ~ * ?. You can also find the chip itself by entering? ~~ ?.

advice

The complete suite of Microsoft 365 offers all the necessary office applications for day-to-day work.


...