What is the Replace function in VBA?
VBA Replace function replaces a given substring (expression) in the source string.
Syntax:
In the examples below we will show you usage and explain the arguments in the Replace function.
An example of using the expression, find and replace arguments
Our expression (source string) in the example below is:
- We want to find the occurrence of “is” as the find argument.
- We will replace “is” with “was”. So “was” is our replace argument.
- Combining these, this is our VBA code where we display the resultant string after using the Replace function.
VBA code:
Sub replace_ex() Dim Str As String Str = "That is good!" MsgBox (Replace(Str, "is", "was")) End Sub
Output:
So, in the above example, we replaced the word “is” with “was”.
The example of using the start argument in the Replace function
You can specify where to start replacement in the source string by using the Start argument.
For example, our source string is:
“This is good”
We specify start = 5 in the example below:
Sub replace_ex() Dim Str As String Str = "This is good!" MsgBox (Replace(Str, "is", "was", 5)) End Sub
Result:
So returned result was only replaced from the 5th character and returned the remaining string after replacement.
Using the count argument example
Consider this string that we will use for our example below:
We only want to replace “is” with “was” for the first sentence and want to get a complete sentence. See how it is done:
Sub replace_ex() Dim Str As String Str = "COBOL is cool! VBA is good" MsgBox "String After Replace = " & (Replace(Str, "is", "was", , 1)) End Sub
Output:
If we had not used Count, the output after using this line:
Does Replace perform case-sensitive replacements?
The default behavior of the Replace function is case-sensitive replacements. The example below shows this:
Sub replace_ex() Dim Str As String Str = "COBOL was Good! VBA is good" MsgBox "String After Replace = " & (Replace(Str, "good", "Awesome")) End Sub
Result:
So, we wanted to replace “good” with “Awesome” and replacement only done for starting with a small ‘g’.
How to perform case-insensitive by Compare argument
So, if you want to perform case-insensitive replacements, the answer is simple:
The compare argument has the following possible constant values:
| Constant | Value | Description |
|---|---|---|
| vbUseCompareOption | -1 | Performs a comparison by using the setting of the Option Compare statement. |
| vbBinaryCompare | 0 | Use this to perform binary comparisons. |
| vbTextCompare | 1 | Used for textual comparisons. |
| vbDatabaseCompare | 2 | For Microsoft Access (Windows only). |
Our answer is the third constant i.e. vbTextCompare. You may either use it or its value 1.
See the same example as above and now the output:
Sub replace_ex() Dim Str As String Str = "COBOL was Good! VBA is good" MsgBox "String After Replace = " & (Replace(Str, "good", "Awesome", , , vbTextCompare)) End Sub
Result:
You can see that both occurrences, irrespective of the case are replaced.
Using the VBA Replace function in Excel cells
Now, as you have seen examples of using all arguments, and hope the function is pretty clear. Let us look at examples of using the VBA Replace function in the Excel sheet.
For this example, we have a number in A3 cell in this format:
457-4575-4574-7
We will write the number to B3 cell by replacing the first two dashes with spaces.
See the code and output below:
Sub replace_ex()
Range("B3").Value = Replace(Range("A3"), "-", "", , 2)
End Sub
Output:
Example of replacing in range of cells
- The example below replaces “-” with space for the range of cells.
- For that, we created a range of A2:A5 cells and then used a For..Loop to use the Replace function for each cell in the range.
- In each iteration, we write the replaced value to the corresponding B-column cell:
Sub replace_ex()
Dim rng_replace As Range
Dim x
Set rng_replace = Range("A2:A5")
x = 2
For Each cell In rng_replace
Range("B" & x).Value = Replace(Range("A3"), "-", " ")
x = x + 1
Next
End Sub
Result:
