Excel Replace Function

Find and Replace are one of the common operations used by every Excel users in their daily life. But do you know Microsoft has provided an inbuilt Replace Function to quickly remove text from a location and change it from a different text.

In this tutorial, we will cover the basics of the Replace Function, including the definition, syntax, semantics, return value and some advanced examples that are useful in the Excel world.

What is Excel Replace Function?

"The REPLACE is an inbuilt Excel function categorized under Text Function. It replaces a part of a text or the entire string (depending on the number of characters you specify) with a certain text value."

The REPLACE function replaces characters in a text string by position. This function is commonly used in situation where the location of the string to be replaced is known or can be easily determined by the user. For example: the financier or Money Management department uses this function primarily in financial analysis to remove text from a cell when the text is in a variable position.

Syntax

=REPLACE (old_text, start_num, num_chars, new_text)

Parameters

1. Old_text (required) - This parameter represents the text that the user wants to replace with another characters.
2. Start_num(required) - This parameter represents the first character within the old_text, that the user wishes to replace.
3. Num_chars(required) - This parameter represents the number of characters to replace.
4. New_text(required) - This parameter represents the replacement text.

Return

The Replace Function returns a part of a text or the entire string with a different string value.

Example

Let's work upon different real-life examples where we can implement the Replace function.

Example 1

Below-given is Excel data where are given a series of password. Now to encrypt them we want to replace the alphabet 'i' with XXXX.

How we can get that done using the Excel Replace function? To understand the step-by-step implementation of this function, follow the steps:

1. Select the cell where you want to put the data after replacing the alphabet 'I' with XXXX.
   
2. Start your function with equals to '=' followed by Replace.
   
3. In the parameters, specify the cell reference of old (old_text), the position from where you want to start replacing (start_num), the position where you want to stop (num_chars) and the text to replace (new_text).
   
4. Press the enter button and you will have the following output where the alphabet 'i' has been replaced by XXXX.
   
5. Follow the same function for all the cells for the given data.

Example 2:

The REPLACE function can only replace the sequence of String characters with another string character. Therefore, we may get wrong or irregular output if we try to apply this function with date, time or number values.

Still, if you want to incorporate the Replace function with other data types, you must first convert them to text or string using Excel's 'Text To Columns' tool.

Suppose in the below given date values, we have to replace the month November (11) with September (9).

Follow the below steps to quickly convert any data type to text and use the Replace function:

1. The first step is to select the cell or the column that you want to convert to text. Since all our date values resides in Column B so we have selected it.
2. Start your function with equals to '=' followed by Replace. In the parameters, specify the cell reference of old (old_text), the position from where you want to start replacing (start_num), the position where you want to stop (num_chars) and the text to replace (new_text).
   Formula Becomes: =REPLACE(B2,4,2,"10")
   
3. As a result, the data will replace the 11 (November month) with digit 10 and give you the output in numbers.
   
4. But if you want to replace the data in the same format which it was earlier. In such cases, we have to convert the data values to text.
5. Select the data you want to convert to text. Go to Data-> Click on Text to column option.
   
6. The Convert Text to Column wizard window will appear. Choose the option "Delimited" and click on Next option.
   
7. In the next window, click on Next, and in the final window panel, select the Text radio button as a data format. Click on the Finish button.
   
8. The Date will be converted to text. Now again apply the Replace function.
   Formula Applied: =REPLACE(B2,4,2,"10")
   
9. Microsoft Excel will finally replace the digit 11 with 10 and give you the following output.
   
10. Drag the formula down to repeat it down the cells. All the dates will be changed to the required format.
    

NOTE: If you want to convert them to data again, you need to apply the text to columns steps and convert it to date format.

Replace Function in VBA

Excel VBA is an interesting platform that can be useful to solve all the problems within a single click. If you have VBA knowledge, you can use Replace function in VBA Editor as well. Though in VBA, the syntax of Replace function is bit different then Excel.

Syntax

Replace (string1, find, replacement, [start, [count, [compare]]])

Parameters

string1 (required): This parameter represents a string or sequence of characters that you want to replace with another set of characters.

Find (required): This parameter signifies a string that the user wants to search instring1.

Replacement (required): This argument is used replacethe findvalue in string1 parameter.

Start (optional): This parameter represents the position instring1that is useful to begin the search. If the user skips this parameter, the REPLACE function will begin the search at position 1.

Count (Optional): This parameter represents the total number of occurrences to replace. If the user skip this parameter, this function will replace all occurrences offindwithreplacement.

Compare (Optional): This parameter takes any one of the following values:

1. vbBinaryCompare: Binary comparison
2. vbTextCompare: Textual comparsion

Examples:

Let's look at some Excel REPLACE function examples and explore how to use the REPLACE function in Excel VBA code:

Step 1: Click on Developer-> Visual Basic.

Step 2: It will open the Developer window. From the above tabs, click on Insert-> Module.

CODE:

Sub Repalce_Example()

Dim LResult As String

Range("B1").Value = Replace(Range("A1"), "Excel", "XL")

End Sub

Things to Remember

1. If you want to remove the entire text and don't want to replace it with other values, usean empty string ("") for thenew_text parameter.
2. The REPLACE function returns an error #VALUE if the user specifies a negative or non-numeric number in thestart_numornum_charsparameters.
3. The REPLACE function can also be applied to numbers, but it returns the output as text.