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.
=REPLACE (old_text, start_num, num_chars, new_text)
The Replace Function returns a part of a text or the entire string with a different string value.
Let's work upon different real-life examples where we can implement the Replace function.
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:
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:
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.
Replace (string1, find, replacement, [start, [count, [compare]]])
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:
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.
Dim LResult As String
Range("B1").Value = Replace(Range("A1"), "Excel", "XL")
Things to Remember