How to Use VBA Replace Function in Excel (11 Applications)

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:

Use VBA Replace to Find a Word and Replace

Use VBA Replace to Find a Word and Replace

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

Use VBA Replace to Find a Word and Replace

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

Use VBA Replace to Find a Word and Replace

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

Apply VBA Replace Function for Case Sensitive Replacement

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.

Apply VBA Replace Function for Case Sensitive Replacement

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

VBA Replace Function to Replace Text in a String

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

VBA Replace Function to Replace Text in a String

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

Replace String with Variables

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

Replace String with Variables

  • Now a new input box will appear.

Replace String with Variables

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

Replace Substring Using VBA Replace Function

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

Replace Substring Using VBA Replace Function

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.

Replace Substring Using VBA Replace Function

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

Replace Substring Using VBA Replace Function

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


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.

Replace a String from a Specific Position Using VBA Replace

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

Replace a String from a Specific Position Using VBA Replace

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

Replace Only the First Occurance of a String Using VBA Replace

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

Replace Only the First Occurance of a String Using VBA Replace


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.

Apply VBA Replace to Replace Specific Occurrences of a String

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

Replace Double Quotes with VBA Replace Function

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

Use VBA Replace Delete Break Line in a Cell

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

Use VBA Replace Delete Break Line in a Cell

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

Remove Spaces Using VBA Replace

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

Remove Spaces Using VBA Replace

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

Remove Spaces Using VBA Replace

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


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.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo