Excel VBA Replace String

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

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

Replace Function

  • 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.”[Start]: 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.[Count]: 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.

How to Replace Text in String using VBA?

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 macroExcel MacroA macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks. read more procedure now.

Code:

Sub Replace_Example()

End Sub

Define the VBA variableDefine The VBA VariableVariable declaration is necessary in VBA to define a variable for a specific data type so that it can hold values; any variable that is not defined in VBA cannot hold values.read more as String.

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.

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.

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 [“Start”]. Look at the below code for your information.

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 [“Count”] parameter of the Replace function.

Below is the code for you.

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.

Things to Remember Here

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

This article 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, practical examples,, and a downloadable template. Below you can find some useful Excel VBA articles: –

  • VBA String to DateVBA IsDateExcel VBA CLngSubString in VBA