In this article, we will demonstrate the use of the VBA Replace function. To illustrate this function to you we will go over several examples with different datasets. After reading this article you will be able to replace any specific string using the VBA Replace function.

---

Download Practice Workbook

You can download the practice workbook from here.

---

Overview of Vba Replace Function

• Description

The VBA Replace function replaces a string with another string.

• Generic Syntax

Replace(expression, find, replace, [ start, [ count, [ compare ]]])

• Argument Description

 

• Returns

Returns string after replacement.

• Available in

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.

---

11 Applications of VBA Replace Function

1. Use VBA Replace to Find a Word and Replace

In this example, we will find specific words from a dataset and replace them with other words. We have the following dataset of six students and their status of present or absent in the class. Now we will find the Absent values. Then we will replace this value with Present. Basically, we will change the status of Present for all students. Just go through the following steps to perform this action:

• In the beginning, go to the Developer tab.
• Next, select the option Visual Basics.

• A new dialogue box will open. From the box go to the Insert tab and select the Module.

• Now, a new blank code window will open.
• Then Insert the following code in the blank part.

Sub Replace_Example1()
Range("B4:C10").Replace What:="Absent", Replacement:="Present"
End Sub

• After that click on the Run We can also press F5 to run the code.

• Finally, we get the value Present as the status of all students.

---

2. Apply VBA Replace Function for Case Sensitive Replacement

By default, the VBA Replace function is case-sensitive. In this method, we will discuss how we can handle case-sensitive replacements. To illustrate this method we have created the following dataset. From the following dataset, we will replace the value NEW YORK. Our process will ignore the value New York which contains lower case letters. We will replace the value NEW YORK with the string CAPITAL. Let’s see how we can do this in simple steps:

• Firstly open a VBA code window.
• Next, Insert the following code:

Sub Replace_Example2()
Range("B4:D10").Replace What:="NEW YORK", Replacement:="CAPITAL", MatchCase:=True
End Sub

• After that, click on the Run icon or press the F5.

• Finally, we can see the value CAPITAL as a replacement for the value NEW YORK.

---

3. VBA Replace Function to Replace Text in a String

We can easily replace a text in a string using the VBA Replace function. To demonstrate this problem we will replace the word Excel from the following dataset. We will use “Microsoft Excel” as a replacement for the word “Excel”. Let’s see the steps of doing this:

• First, open a new VBA code box.
• Next, input the following code in the box:

Sub VBA_Replace2()
Dim X As Long
X = Range("B5000").End(xlUp).Row
Dim Y As Long
For Y = 3 To X
Range("C" & Y).Value = Replace(Range("B" & Y), "Excel", "Microsoft Excel")
Next Y
End Sub

• After that, press F5 or click on the Run.

• So, we can see the value “Microsoft Excel” in place of the string “Excel”.

---

4. Replace String with Variables

In the previous example, we used a fixed string as a replacement. In this example, we will see how we can input variables as a replacement. We will continue with our previous dataset for this method also. Now, just follow the below instructions to perform this action:

• In the beginning, open a new VBA code box.
• Next, insert the following code:

Sub Example2()
Dim X As Long
X = Range("B5000").End(xlUp).Row
Dim Y As Long
Dim Str, Str1, Str2 As String
Str = Range("B5000").End(xlUp).Row
Str1 = InputBox("Enter a String")
For Y = 3 To X
Range("C" & Y).Value = Replace(Range("B" & Y), "Excel", Str1)
Next Y
End Sub

• Then click on the Run We can also use the F5 key.

• Now a new input box will appear.

• Here, enter the value “Microsoft Excel” as an input variable.
• After that, press OK.

• Finally, we get the new string “Microsoft Excel” as a replacement for the string “Excel”.

---

5. Replace Substring Using VBA Replace Function

In this example, we will replace a substring. We will consider the string “Microsoft Excel 2019”. We will replace the substring part “19” with “22”. Let’s see the steps to solve this:

• First, open a new VBA code window.
• Next insert the following code:

Sub ReplaceExample_1()
MsgBox Replace("Microsoft Excel 2019", "19", "22")
End Sub

• Here, “19” is the value to find and “22” is the replacement value.
• Now click on the Run icon or press the F5.

• As a result, we get the replaced value in a message box.

Now, the current text is “Microsoft Excel 2022”. In this part, we will show how we can replace “o” with “@”. Here will start the process of replacement from position 5. To do this in a message box follow the below steps:

• Firstly, insert the following code in a VBA code window:

Sub ReplaceExample_1()
MsgBox Replace("Microsoft Excel 2022", "o", "@", 5)
End Sub

• Here, we are taking a new argument “start” with the value 5.
• Click on the Run icon.

• As a result, a message box will show up. We can see replacement start from 5th.

Again if we want to start the replacement process from the number 6 position just take the start argument value 6. Insert the previous code just with the change in the start argument:

Sub ReplaceExample_1()
MsgBox Replace("Microsoft Excel 2022", "o", "@", 6)
End Sub

• Click on the Run.

• So, we can see how the replacement starts from position 6.

---

6. Replace a String from a Specific Position Using VBA Replace

Now we will replace a string from a specific position in our text using the VBA Replace function. Assume we have a text “America has great Economy. America was discovered by Christopher Colombus”. We want to replace “America” with “The United States of America” only for the second occurrence. To understand this method follow the below steps:

• In the beginning, open a VBA code window.
• Next, insert the following code:

Sub Replace_Example2()
Dim NewString As String
Dim MyString As String
Dim FindString As String
Dim ReplaceString As String
MyString = "America has great Economy. America was discovered by Christopher Colombus"
FindString = "America"
ReplaceString = "The United States of America"
NewString = Replace(MyString, FindString, ReplaceString, Start:=29)
MsgBox NewString
End Sub

• Here we use the value of start argument 28.
• Now click on the Run icon.

• So, we can see that the string “America” is replaced by “The United States of America” after the 28th Character.

• Here, the box contains 28 characters with spaces.

---

7. Replace Only the First Occurance of a String Using VBA Replace

Suppose we have the text “Facebook is a social media platform. The CEO of Facebook is Mark Zuckerberg”. In this text, the string “Facebook” occurs two times. We want to replace “Facebook” with “Meta” only for the first occurrence. To perform this action follow the below steps:

• Firstly, in the VBA code box insert the following code:

Sub Replace_Example3()
Dim NewString As String
Dim MyString As String
Dim FindString As String
Dim ReplaceString As String
MyString = "Facebook is a social media platform. The CEO of Facebook is Mark Zuckerberg"
FindString = "Facebook"
ReplaceString = "Meta"
NewString = Replace(MyString, FindString, ReplaceString, Count:=1)
MsgBox NewString
End Sub

• Here use the count argument value 1.
• Then, click on the Run.

• Finally, we can see the string “Meta” as the replacement of “Facebook” only for the first occurrence.

---

8. Apply VBA Replace to Replace Specific Occurrences of a String

In the previous example, we only replaced the first occurrence for a string whereas this example will replace for specific occurrences of a string. We will consider the following text:

“Red Light, Green Light, Blue Light, Yellow Light”

To illustrate this method, we will replace the string “Light” with “Ball” for every first two occurrences. We will go through the following steps to demonstrate this method:

• Firstly, in the VBA code window insert the following code:

Sub ReplaceExample_3()
MsgBox Replace("Red Light, Green Light, Blue Light, Yellow Light", "Light", "Ball", , 2)
End Sub

• Here, we set the value of the start argument blank and the count argument 2.
• Now, click on the Run.

• Lastly, we can see the replaced value of “Light” with “Ball” for the first two occurrences.

---

9. Replace Double Quotes with VBA Replace Function

In this section, we will remove double quotes with the VBA Replace function. We will consider the string “VBA””Application””” and then will omit the double quote. Let’s see how we can do this:

• Firstly, in the VBA code box insert the following code:

Sub ReplaceExample_5()
Dim StrEx As String
StrEx = "VBA""Application"""
MsgBox StrEx
End Sub

• Then, click on the Run.

• So, we can get the string value in a message box without double-quotes.

---

10. Use VBA Replace Delete Break Line in a Cell

From the following dataset, we can see breaks among the lines in cell B5. You can put a break between lines by pressing Alt + Enter. Now we will remove the breaks among lines Using the VBA Replace function with the following steps:

• First, open the VBA code window box and insert the following code:

Sub RemoveLineBreaks()
For Each Cell In Selection
Cell.Value = Replace(Cell.Value, Chr(10), ", ")
Next
End Sub

• Now, click on the Run.

• Finally, we can see the lines without a line break.

---

11. Remove Spaces Using VBA Replace

In the following dataset, we can see the spaces between letters in the values of column B. In this example, we will remove these spaces with the help of the VBA Replace function. Let’s see the steps of performing this action:

• In the beginning, open the VBA code box and insert the following code:

Sub RemoveSpaces()
Dim OriginalText As String
Dim CorrectedText As String
OriginalText = Range("B5").Value
CorrectedText = Replace(OriginalText, " ", "")
Range("B5").Offset(, 1).Value = CorrectedText
End Sub

• Here, the Range value is B5. So, this code will remove the spaces from the value of cell B5.
• Then click on the Run.

• So, we can see the value of cell B5 in cell C5 without spaces.

• Finally, by changing the Range value for corresponding cells we get all the values of column B in column C without spaces.