EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA String and Text Functions VBA Replace String
 

VBA Replace String

Madhuri Thakur
Article byMadhuri Thakur

Updated June 26, 2023

VBA Replace String

 

 

Excel VBA Replace String

Excel VBA Replace looks for a substring within a list and replaces the substring with a second substring. When we use Excel VBA Replace Strings in Data, there are some situations when we need to replace some character or a string from a sentence. If we have a large set of data that we want to replace, it can be a tedious task to do it manually, but with this function, it is very easy to provide the arguments and replace the string.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

The syntax for Replace String function:

Replace ( Expression as String, Old String, New String, Start, Count, Compare)
Now the expression is the main string from where we want to remove the old string and new string, which will replace the old string. The other arguments are optional. If we compare this function to the Excel worksheet substitute function, we will find that Replace in VBA replaces the substitute function in Excel.

How to Replace Text in String Using VBA?

We will learn how to Replace Text in String using the VBA Code in Excel.

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

Example #1

Let us start with a basic example of using the replace function, where we will replace a simple word name from the sentence “My name is Anand.” Here we will replace Anand with Aran. For this, follow the below steps:

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select module.

Insert Module

Step 2: Insert a new subprocedure, for example, 1.

Code:

Sub Example1()

End Sub

VBA Replace String Example 1-1

Step 3: Declare a variable as a string.

Code:

Sub Example1()

Dim Str As String

End Sub

Variable Example 1-2

Step 4: Now let us use replace function while providing a string as the expression to the variable.

Code:

Sub Example1()

Dim Str As String
Str = Replace("My name is Anand", "Anand", "Aran")

End Sub

VBA Replace String Example 1-3

Step 5: Let us use the Msgbox function to display the value we have in this variable.

Code:

Sub Example1()

Dim Str As String
Str = Replace("My name is Anand", "Anand", "Aran")
MsgBox Str

End Sub

VBA Replace String Example 1-4

Step 6: When we execute the code.

VBA Replace String Example 1-5

Example #2

Let us begin with the second example, where we can use the arguments used in the replace function as the variables. We will use a different statement for this example. For this, follow the below steps:

Step 1: Let us start another sub-procedure in the same module.

Code:

Sub Example2()

End Sub

VBA Replace String Example 2-1

Step 2: Now, let us declare three different variables as a string.

Code:

Sub Example2()

Dim Str, Str1, Str2 As String

End Sub

Declare Three Different Variables Example 2-2

Step 3: We can provide any string statement in the first variable.

Code:

Sub Example2()

Dim Str, Str1, Str2 As String
Str = " I am a Good Boy"

End Sub

String statement Example 2-3

Step 4: Now, in the second variable, we can use the Input Box function to get input from the user.

Code:

Sub Example2()

Dim Str, Str1, Str2 As String
Str = " I am a Good Boy"
Str1 = InputBox("Enter a String")

End Sub

Input Box function Example 2-4

Step 5: Now, in the third variable, we can use the replace function to replace the second string with the desired string.

Code:

Sub Example2()

Dim Str, Str1, Str2 As String
Str = " I am a Good Boy"
Str1 = InputBox("Enter a String")
Str2 = Replace(Str, "Boy", Str1)

End Sub

VBA Replace String Example 2-5

Step 6: The Msgbox function will display the result in the third string.

Code:

Sub Example2()

Dim Str, Str1, Str2 As String
Str = " I am a Good Boy"
Str1 = InputBox("Enter a String")
Str2 = Replace(Str, "Boy", Str1)
MsgBox Str2

End Sub

Msgbox function Example 2-6

Step 7: When we execute the code above and give input as “girl.”

VBA Replace String Example 2-8

Example #3

Now we will discuss one of the optional arguments in the function: the start option, where we can define from which position we want. For this, follow the below steps:

Step 1: Let us start another sub-procedure in the same module.

Code:

Sub Example3()

End Sub

VBA Replace String Example 3-1

Step 2: Now, let us declare three different variables as a string.

Code:

Sub Example3()

Dim Str, Str1, Str2 As String

End Sub

VBA Replace String Example 3-2

Step 3: We can provide any string statement in the first variable.

Code:

Sub Example3()

Dim Str, Str1, Str2 As String
Str = " I am a Good Boy"

End Sub

VBA Replace String Example 3-3

Step 4: Now, in the second variable, we can use the Input Box function to get input from the user.

Code:

Sub Example3()

Dim Str, Str1, Str2 As String
Str = " I am a Good Boy"
Str1 = InputBox("Enter a String")

End Sub

Input Box function Example 3-4

Step 5: Now, in the third variable, we can use the replace function to replace the second string with the desired string and give the start position as 7.

Code:

Sub Example3()

Dim Str, Str1, Str2 As String
Str = " I am a Good Boy"
Str1 = InputBox("Enter a String")
Str2 = Replace(Str, "Boy", Str1, Start:=7)

End Sub

VBA Replace String Example 3-5

Step 6: We can use the Msgbox function to show the result and when we execute the code, which will ask us for a string.

Code:

Sub Example3()

Dim Str, Str1, Str2 As String
Str = " I am a Good Boy"
Str1 = InputBox("Enter a String")
Str2 = Replace(Str, "Boy", Str1, Start:=7)
MsgBox Str2

End Sub

Msgbox function Example 3-6

Step 7: We will see the final result when we press Ok.

VBA Replace String Example 3-7

Example #4

Now there is another optional argument: the number of times we want to replace a string. For this, follow the below steps:

Step 1: Let us start another sub-procedure in the same module.

Code:

Sub Example4()

End Sub

VBA Replace String Example 4-1

Step 2: Now, let us declare three different variables as a string.

Code:

Sub Example4()

Dim Str, Str1, Str2 As String

End Sub

VBA Replace String Example 4-2

Step 3: We can provide any string statement in the first variable.

Code:

Sub Example4()

Dim Str, Str1, Str2 As String
Str = " I am a Good Boy, but you are a bad boy"

End Sub

VBA Replace String Example 4-3

Step 4: In the second variable, we can use the Input Box function to get input from the user.

Code:

Sub Example4()

Dim Str, Str1, Str2 As String
Str = " I am a Good Boy, but you are a bad boy"
Str1 = InputBox("Enter a String")

End Sub

Input Box function Example 4-4

Step 5: Now, in the third variable, we can use the replace function to replace the second string to the desired string as shown in below screenshot and use the number of instances we want to replace the string as follows and then use the msgbox function to display the result.

Code:

Sub Example4()

Dim Str, Str1, Str2 As String
Str = " I am a Good Boy, but you are a bad boy"
Str1 = InputBox("Enter a String")
Str2 = Replace(Str, "Boy", Str1, Count:=1)

End Sub

VBA Replace String Example 4-5

Step 6: Use the number of instances we want to replace the string as follows, and then use the Msgbox function to display the result.

Code:

Sub Example4()

Dim Str, Str1, Str2 As String
Str = " I am a Good Boy, but you are a bad boy"
Str1 = InputBox("Enter a String")
Str2 = Replace(Str, "Boy", Str1, Count:=1)
MsgBox Str2

End Sub

VBA Replace String Example 4-6

Step 7: Once we execute the code and provide a second string as “girl,” we will see the following result.

VBA Replace String Example 4-7

Explanation of VBA Replaces String:

We have seen from the above examples that the Replace function replaces the Excel substitute function. It has three mandatory arguments, and other arguments are optional. We can use the optional arguments together or one at a time. Start refers to the position of the string, whereas count refers to the number of times we want to replace the old string with the new string.

How to Use VBA Replace String?

The method to use this function is straightforward and similar to the Excel worksheet function. This function needs three mandatory arguments:

  1. First is the expression or the main string.
  2. Then we have to provide a target string that needs to be replaced.
  3. And the final argument is the string which is the new string that needs to be replaced.

Things to Remember

There are a few things that we need to remember about Replace string in VBA, such as follows:

  1. There are three arguments in the Replace function of Excel, and there are two optional arguments.
  2. The two optional arguments are either for the number of times we want to replace or the position where we want to replace.
  3. This function is similar to a substitute function in Excel.

Recommended Articles

This is a guide to the VBA Replace String. Here we discuss How to Replace Text in String in Excel VBA, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. VBA RGB
  2. VBA CDEC
  3. Excel VBA MsgBox
  4. VBA IsError

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download VBA Replace String Excel Template

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW