VBA REPLACE Function (Syntax + Example)

The VBA REPLACE function is listed under the text category of VBA functions. When you use it in a VBA code, it replaces a substring from a string with a new sub-string. In simple words, you can use REPLACE to replace a part of text with another text and it returns that new text in the result.

Syntax

Replace(Expression, Find, Replace, [Start], [Count], [Compare])

Arguments

• Expression: The original string in which you want to search.
• Find: The sub-string which you want to find.
• Replace: The sub-string with which want to replace the find sub-string.
• [Start]: An integer to specify the position from where you want to start the search [This is an optional argument and if omitted VBA takes 1 by default].
• [Count]: The number of occurrences of the Find sub-string that you want to replace [This is an optional argument and if omitted VBA takes -1 which means to replace all occurrences to replace by default].
• [Compare]: A string value to define the comparison to make while filtering array. [This is an optional argument and if omitted VBA takes vbBinaryCompare by default].
  • vbBinaryCompare: For binary comparison.
  • vbTextCompare: For text comparison.
  • vbDatabaseCompare: For Database Comparison.

Example

To practically understand how to use the VBA REPLACE function, you need to go through the below example where we have written a vba code by using it:

Sub example_REPLACE()
Range("B1").Value = Replace(Range("A1"), "Excel", "XL")
End Sub

In the above code, we have used the REPLACE to replace the string “Excel” with “XL” and return the result in cell B1.

Notes

• REPLACE is not case sensitive function.