VBA Replace String

Replace is both Worksheet function as well as VBA function. This function helps us to replace the particular word from the string with another string. It works similarly to the Substitute function in VBA.

While dealing with test string or text data values, it is obvious to replace or substitute something with something else, joining two cell data into one or splitting one cell data into multiple things. These are all common tasks we do day in and day out at our workplace.

So, how do we replace one word in the string with another? For example, if the string is "India is a developing country and India in the Asian Country" from this string, we need to replace the word "India" and change it to "Bharath."

It is possible by using the Replace function. This article will show you how to replace strings in VBA coding.

• Expression: This is nothing but the original string value from which we are trying to replace something with something. For example, below is the expression string – "India is a developing country and India is the Asian Country."
• Find String: What is the string we are trying to replace? For example, in the Expression string, we are trying to replace the word "India."
• Replace String: What substitute string do we replace the Find String with? So, in this case, we are trying to replace the word "India" with "Bharath."
• : This is an optional parameter. In the above string (Expression), we have two words, "India," so from which position of the Find String do we need to start the replacement process? For example, if we say 2, it will start to replace the word "India" from the second position onwards.
• : If the Find String appears multiple times in Expression, how many words do we need to replace?

For example, if the word "India" appears 5 times and you supply the count as 3, it will replace only the first 3 "India" words.

Example #1

Now, we will try to replace the word "India" with "Bharath" from the below string value.

“India is a developing country and India in the Asian Country.”

First, start the excel macro procedure now.

Code:

Sub Replace_Example() End Sub

Define the VBA variable as String.

Code:

Sub Replace_Example() Dim NewString As String End Sub

In this variable, we will show a new string value after replacing the word “India” with “Bharath.” For this variable, open the Replace function.

The first argument of this function is “Expression,” i.e., from which string we are trying to replace a word, so copy and paste the string “India is a developing country and India in the Asian Country.”

The next argument is "Find String," i.e., which word we need to replace, i.e., "India."

The next argument is "Replace String," i.e., with which string we need to replace the word "India," i.e., "Bharath."

As of now, ignore the remaining arguments. Now, show the result in the message box.

Code:

Sub Replace_Example() Dim NewString As String NewString = Replace("India is a developing country and India is the Asian Country", "India", "Bharath") MsgBox NewString End Sub

Let us run the code using the F5 key or manually and see the new string result.

Look at the above result. Wherever we had the word "India," it has been replaced with the word "Bharath."

Example #2

Now, we will see how to use the same code with variables. Look at the below code.

Code:

Sub Replace_Example1() Dim NewString As String Dim MyString As String Dim FindString As String Dim ReplaceString As String MyString = "India is a developing country and India is the Asian Country" FindString = "India" ReplaceString = "Bharath" NewString = Replace(MyString, FindString, ReplaceString) MsgBox NewString End Sub

In the above code, we have declared an extra three variables.

Dim MyString As String Dim FindString As String Dim ReplaceString As String

For these variables, we have assigned values. Instead of supplying the Expression String, Find String, and Replace String, we will supply only variables to the Replace function.

This code also gives the same result, but the only difference is we have used variables instead of a direct supply of values to the function.

Example #3

Assuming you want to replace the word "India" only from the second position, then we need to use the Replace function parameter . Look at the below code for your information.

Code:

Sub Replace_Example2() Dim NewString As String Dim MyString As String Dim FindString As String Dim ReplaceString As String MyString = "India is a developing country and India is the Asian Country" FindString = "India" ReplaceString = "Bharath" NewString = Replace(MyString, FindString, ReplaceString, Start:=34) MsgBox NewString End Sub

The only extra thing we have added from the previous code is the "Start" parameter as 34. Now, run the code and see the result.

Now, we can see only the string after the 34th character of the string with "India" replaced with "Bharath."

Example #4

Now, for example, if we want to replace only the first occurrence of the word "India" with "Bharath," then we need to use the parameter of the Replace function.

Below is the code for you.

Code:

Sub Replace_Example3() Dim NewString As String Dim MyString As String Dim FindString As String Dim ReplaceString As String MyString = "India is a developing country and India is the Asian Country" FindString = "India" ReplaceString = "Bharath" NewString = Replace(MyString, FindString, ReplaceString, Count:=1) MsgBox NewString End Sub

Run the code manually or through the F5 key and see the result.

As you can see above, it has replaced only the first occurrence of the word "India" with "Bharath," and the second instance remains the same.

• Replace is a string function family in VBA.
• In VBA, the replace function replaces all the supplied words with replaced strings if the count parameter is not specified.
• The start parameter will delete the number of characters supplied and show the remaining result.