VBA Replace String

Excel 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 similar to the Substitute function in VBA.

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

So, how we replace one word in the string with another word? 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 changed to “Bharath.”

This is possible by using the Replace function. In this article, we will show you how to replace strings in VBA codingVBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

VBA-Replace-String.png

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Replace String (wallstreetmojo.com)

Replace Function

VBA Replace Syntax
  • 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 in 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 is the substitute string we replacing the Find String with? So, in this case, we are trying to replace the word “India” with “Bharath.”
  • [Start]: This is an optional parameter. In the above string (Expression), we have two words, “India,” so from which position of the Find String, 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.
  • [Count]: If the Find String appearing multiple times in Expression, then how many words we need to replace.

For example, if the word “India” appearing 5 times and if you supply the count as 3, then it will replace only the first 3 “India” words.

How to Replace Text in String using VBA?

You can download this VBA Replace String Excel Template here – VBA Replace String Excel Template

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
VBA Replace String Example 1

Define the VBA variable as String.

Code:

Sub Replace_Example()

Dim NewString As String

End Sub
VBA Replace String Example 1-1

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

VBA Replace String Example 1-2

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.”

VBA Replace String Example 1-3

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

VBA Replace String Example 1-4.png

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

Bharath Example 1-5

Ok, as of now, ignore 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
VBA Replace String Example 1-6

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

VBA Replace String Example 1-7

Ok, 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
Example 2

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

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

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

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

Example #3

Assume you want to replace the word “India” only from the second position, then we need to use the Replace function parameter [“Start”]. 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
Example 3

Only one extra thing we have added from the previous code is the “Start” parameter as 34. Now run the code and see the result.

VBA Replace String Example 3-1

Now we can see only string after the 34th character of the string with “India” replacing with “Bharath.”

space character Example 3-2

Example #4

Now for an example, if we want to replace only the first occurrence of the word “India” with “Bharath,” then we need to use the [“Count”] 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
Example 4

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

VBA Replace String Example 4-1

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

Things to Remember Here

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

This has been a guide to VBA Replace String. Here we learn to replace the particular word from the string with another string using the Replace function along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>