Examples of working with text function REPLACE in Excel

REPLACE function is included in the text functions of MS Excel and is intended to replace a specific area of the text string containing the source text on the specified text line (new text).



How does the REPLACE function in Excel work?

Example 1. In order to study in detail the operation of this function, we consider one of the simplest examples. Suppose we have several words in different columns, we need to get new words using the original ones. For this example, in addition to our main function REPLACE, we also use the RIGHT function — this function serves to return a certain number of characters from the end of a line of text. That is, for example, we have two words: milk and a skating rink, as a result we must get the word hammer.

REPLACE function in Excel and examples of its use

  1. Create a table with words on the sheet of the Excel spreadsheet workbook, as shown in the figure:
    2. Example 1.
  2. Next, on the sheet of the workbook, we will prepare an area for placing our result - the resulting word “hammer”, as shown below. Place the cursor in cell A6 and call the function REPLACE:
    3. FORMULAS.
  3. Fill the function with the arguments shown in the figure:
    4. REPLACE.

Let us explain the choice of these parameters as follows: cell A2 was chosen as the beginning of the text, the number 5 was set as beginning_, since it is from the fifth position of the word “Milk” we don’t take characters for our final word, the number_ of signs was set equal to 2, since this number It is not taken into account in the new word, as the new text, the set option RIGHT with the parameters of the cell A3 and taking the last two characters "ok".

Next, click on the "OK" button and get the result:

examples of its use. 

How to REPLACE a piece of text in Excel cell?

Example 2. Consider another small example. Suppose we have columns of words in the cells of the Excel spreadsheet. It is necessary to replace their letters in certain places so as to convert them.

  1. Let's create a tablet with words on the sheet of an Excel workbook, as shown in the figure:
    2. Example 2.
  2. Further, on the same sheet of the working book we will prepare an area for placing our result - modified words. Fill the cells with two types of formulas as shown in the picture:
    3. piece of text in cell.

Download examples text function REPLACE in Excel

Note! In the second formula, we use the operator “&” to add the character "s" to the male surname to convert it to the female. To solve this problem, one could use the function =CONCATENATE(B3,"s") instead of the formula =B3&"s" - the result is identical. But today it is strongly recommended to abandon this formula as it has its limitations and is more demanding on resources in comparison with a simple and convenient ampersand operator.


en ru