Method 1 – Use VBA Replace to Find a Word and Replace
We have the following dataset of six students and their status of present or absent in the class. We’ll find the Absent values and replace these values with Present.
- Go to the Developer tab.
- Select the option Visual Basic.
- A new dialogue box will open. From the box, go to the Insert tab and select the Module.
- A new blank code window will open.
- Insert the following code in the blank part.
Sub Replace_Example1()
Range("B4:C10").Replace What:="Absent", Replacement:="Present"
End Sub- Click Run or press F5 to run the code.
- We get the value Present as the status of all students.
Method 2 – Apply the VBA Replace Function for Case Sensitive Replacement
The VBA Replace function is case-sensitive. From the following dataset, we’ll replace the value NEW YORKwith the string CAPITAL. Our process will ignore the value New York, which contains lowercase letters.
- Open a VBA code window.
- Insert the following code:
Sub Replace_Example2()
Range("B4:D10").Replace What:="NEW YORK", Replacement:="CAPITAL", MatchCase:=True
End Sub- Click Run or press the F5.
- See the value CAPITAL as a replacement for the value NEW YORK.
Method 3 – VBA Replace Function to Replace Text in a String
We’ll use “Microsoft Excel” as a replacement for the word “Excel”.
- Open a new VBA code box.
- 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- Press F5 or click Run.
- See the value “Microsoft Excel” in place of the string “Excel”.
Method 4 – Replace a String with Variable Inputs
We used a fixed string as a replacement. We can also input variables as a replacement.
- Open a new VBA code box.
- 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- Click Run. We can also use the F5 key.
- A new input box will appear.
- Enter the value “Microsoft Excel” as an input variable.
- Press OK.
- Get the new string “Microsoft Excel” as a replacement for the string “Excel”.
Method 5 – Replace a Substring Using the VBA Replace Function
We will find the string “Microsoft Excel 2019” and replace the substring part “19” with “22”.
- Open a new VBA code window.
- Insert the following code:
Sub ReplaceExample_1()
MsgBox Replace("Microsoft Excel 2019", "19", "22")
End Sub- “19” is the value to find, and “22” is the replacement value.
- Click on the Run icon or press the F5.
- Get the replaced value in a message box.
The current text is “Microsoft Excel 2022”. We will show how we can replace “o” with “@” and cut off the first four characters.
- Insert the following code in a VBA code window:
Sub ReplaceExample_1()
MsgBox Replace("Microsoft Excel 2022", "o", "@", 5)
End Sub- We are taking a new argument, “start,” with the value 5.
- Click Run.
- A message box will show up.
To start the replacement process from the number 6 position just take the start argument value 6.
Sub ReplaceExample_1()
MsgBox Replace("Microsoft Excel 2022", "o", "@", 6)
End Sub- Click Run.
- See how the replacement starts from position 6.
Method 6 – Replace a String from a Specific Position
We have a text “America has great Economy. America was discovered by Christopher Colombus”. We’ll replace “America” with “The United States of America” only for the second occurrence.
- Open a VBA code window.
- 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- Use the value of start argument 28.
- Click on the Run icon.
- See that the string “America” is replaced by “The United States of America” after the 28th Character.
- The box contains 28 characters with spaces.
Method 7 – Replace Only the First Occurrence of a String
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.
- 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- Use the count argument value 1.
- Click Run.
- See the string “Meta” as the replacement of “Facebook” only for the first occurrence.
Method 8 – Apply VBA Replace to Replace Specific Occurrences of a String
We will consider the following text: “Red Light, Green Light, Blue Light, Yellow Light”
We’ll replace “Light” with “Ball” for the first two occurrences.
- 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- Set the value of the start argument blank and the count argument 2.
- Click Run.
- See the replaced value of “Light” with “Ball” for the first two occurrences.
Method 9 – Replace Double Quotes with a VBA Replace Function
Consider the string “VBA””Application””” where we’ll omit the double quote.
- In the VBA code box, insert the following code:
Sub ReplaceExample_5()
Dim StrEx As String
StrEx = "VBA""Application"""
MsgBox StrEx
End Sub- Click Run.
- Get the string value in a message box without double quotes.
Method 10 – Use VBA Replace to Delete LineBreaks in a Cell
We have a large cell with three sentences divided by line breaks.
- 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- Click on the Run.
- See the lines without a line break.
Method 11 – Remove Spaces Using VBA Replace
There are spaces between letters in the values of column B. We will remove these spaces with the help of the VBA Replace function.
- 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- The Range value is B5. This code removes the spaces from cell B5’s value.
- Click on the Run.
- We can see the value of cell B5 in cell C5 without spaces.
- By changing the Range value for corresponding cells, we get all the values of column B in column C without spaces.
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
What is the difference between substitute and replace in Excel VBA?
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.
Should I use sub or function VBA?
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.
Is VBA better than formulas?
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
Download the Practice Workbook
Related Articles
- How to Use VBA RTrim Function (5 Suitable Examples)
- Use VBA While Wend Statement in Excel (4 Examples)
- Use VBA DateDiff Function in Excel (9 Examples)
- How to Use VBA Switch Function (6 Suitable Examples)
