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
| ARGUMENT | REQUIREMENT | EXPLANATION |
|---|---|---|
| expression | Required | The string that we want to replace |
| find | Required | Finds the substring that we will replace. |
| replace | Required | The string that we will use as the replacement. |
| start | Optional | The starting position from where we want to start to find and replace. If any value is not given excel takes 1 as the default value. |
| count | Optional | It describes the number of replacements we want to make. If any value is not provided excel takes 1 as the default value. This means it will replace all the possible terms. |
| compare | Optional | Indicates the type of comparison while replacing a string. There can be three types of values:
vbBinaryCompare: It’s the default value and implements a binary comparison vbTextCompare: implement a text comparison vbDatabaseCompare: implement a database comparison |
- 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.
Read More: How to Use VBA Case Statement (13 Examples)
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”.
Read More: How to Use VBA Str Function in Excel (4 Examples)
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”.
Read More: How to Use VBA StrComp in Excel (5 Common Examples)
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.
Read More: How to Call a Sub in VBA in Excel (4 Examples)
Similar Readings
- VBA Date Function (12 Uses of Macros with Examples)
- How to Use the Left Function in VBA in Excel (2 Examples)
- Use VBA DIR Function in Excel (7 Examples)
- How to Use MsgBox Function in Excel VBA (A Complete Guideline)
- How to Use VBA SPLIT Function in Excel (5 Examples)
6. Replace a String from a Specific Position
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
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 to 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.
Read More: How to Remove Duplicates in Excel Sheet (7 Methods)
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.
Read More: How to Use VBA Space Function in Excel (3 Examples)
Things to Remember
- VBA Replace is a case-insensitive function.
- You cannot omit the required arguments.
- Use MatchCase property for applying case sensitivity.
Frequently Asked Questions
1. What is the difference between substitute and replace in Excel VBA?
Ans: The main difference between SUBSTITUTE and REPLACE in Excel VBA is that SUBSTITUTE is a built-in Excel function used in worksheet formulas to replace specific instances of a substring, while Replace is a VBA method used to perform find and replace operations within strings, cell values, or specified ranges in VBA code.
2. Should I use sub or function VBA?
Ans: It depends on what you are up to. Use Sub for procedures that do not return a value and Function for procedures that return a value.
3. Is VBA better than formulas?
Ans: VBA and formulas serve different purposes; VBA provides more flexibility and automation options, while formulas are suitable for straightforward calculations and data manipulation.
Excel VBA Replace: Knowledge Hub
- Replace Text in String Using VBA
- Find and Replace a Text in a Range
- Find and Replace Text in Column
- Find and Replace Multiple Values
- Apply Macro to Find and Replace from List
- Replace Blank Cells with Text Using VBA
- Replace Character in String by Position
- Find and Replace Text in Word Document
- Excel VBA Substitute
Conclusion
In this article, we have tried to encapsulate almost all the applications of the VBA Replace function. To practice yourself download the practice workbook added with this article. If you feel any confusion just leave a comment in the below box. We will try to answer as soon as possible. Stay tuned with us for more interesting solutions to Microsoft Excel problems.
