There are situations when in the document you need to replace one character (or group of characters) to another. The reasons can be a set, ranging from a banal error, and, ending with a remote template or removing spaces. Let's find out how to quickly replace the characters in Microsoft Excel.
Ways to replace characters in Excel
Of course, the easiest way to replace one character to another is manual editing cells. But, as practice shows, not always this method is the easiest in large-scale tables, where the number of characters that need to be changed can reach a very large amount. Even the search for the right cells can be spent a significant amount of time, not to mention the time spent on editing each of them.Fortunately, the Excel program has a "Find and Replace" tool, which will help to quickly find the necessary cells, and will replace the symbols in them.
Search with replacement
A simple replacement with a search involves the replacement of one serial and fixed set of characters (numbers, words, signs, etc.) on the other after these characters will be found using a special built-in program tool.
- Click on the button "Find and highlight", which is located in the "Home" tab in the Editing Settings block. In the list that appeared after this list, we make the transition to "Replace".
- The "Find and Replace" window opens in the Replace tab. In the "Find" field, we enter the number, words or symbols that you want to find and replace. In the "Replace on" field, perform data entry to which replacement will be made.
As you can see, at the bottom of the window there are replacement buttons - "Replace everything" and "Replace", and the search buttons - "Find all" and "Find Next". Click on the "Find Next" button.
- After that, a search for the document is desired. By default, the search direction is made line. The cursor stops in the first result that coincided. To replace the contents of the cell, click on the "Replace" button.
- To continue the search for data, we click on the "Find Next" button. In the same way, we change the following result, etc.
You can find all the results satisfying the results immediately.
- After entering the search query and replace characters, click on the "Find All" button.
- Search for all relevant cells. Their list in which the value is indicated and the address of each cell, opens at the bottom of the window. Now you can click on any of the cells in which we want to replace, and click on the "Replace" button.
- Replacing the value will be executed, and the user can continue to continue in the search results to look for the result to it for a re-procedure.
Automatic replacement
You can automatically replace the press of just one button. To do this, after entering the replaceable values, and the values for which the replacement is made, click the "Replace All" button.
The procedure is performed almost instantly.
Pros of this method - speed and convenience. The main minus is that you must be sure that the entered characters need to be replaced in all cells. If in previous ways it was possible to find and select the necessary cells to change, then when using this option, such an opportunity is excluded.
Lesson: How to replace the point on the comma in Excel
Extra options
In addition, there is an extended search and replacement for additional parameters.
- Being in the "Replace" tab, in the "Find and Replace" window, click on the Parameters button.
- Opened window of additional parameters. It is almost identical to the advanced search window. The only difference is the presence of the settings block "Replace on".
The entire bottom of the window is responsible for the search for data, the replacement of which should be performed. Here you can set, where to search (on the sheet or in the entire book) and how to search (by line or by columns). In contrast to the usual search, the search for replacement can be performed exclusively by formulas, that is, the values that are specified in the line of formulas during the selection of the cell. In addition, immediately, by installing or removing checkboxes, can I specify whether to take into account when searching the case of letters, look for precise compliance in the cells.
Also, you can specify among the cells of which format will be searched. To do this, click on the "Format" button opposite the "Find" option.
After that, a window will open in which you can specify the format of the search cells.
The only setting of the value for insertion will be the same cell format. To select the format of the inserted value, we click on the button of the same name opposite the "Replace ..." parameter.
It opens exactly the same window as in the previous case. It is installed how cells will be formatted after replacing their data. You can set alignment, numeric formats, cell color, borders, etc.
Also, by clicking on the appropriate item from the drop-down list under the "Format" button, you can set the format identical to any selected cell on the sheet, it is enough to highlight it.
An additional search limiter may be an indication of the range of cells, among which will be selected and replaced. For this, it is enough just to highlight the desired range manually.
- Do not forget in the field "Find" and "replace on ..." to enter the corresponding values. When all settings are specified, select the method of execution of the procedure. Either click on the "Replace All" button, and the replacement occurs automatically, according to the data entered, or click on the "Find all" button, and separately replacing the algorithm in each cell according to the algorithm.
Lesson: How to make a search in Excel
As you can see, Microsoft Excel provides a fairly functional and convenient tool for searching and replacing data in tables. If you need to replace absolutely all the same type to a specific expression, then this can be done by pressing only one button. In case the sample must be done in more detail, then this feature is fully provided in this tabular processor.