How to use the VBA REPLACE Function (Syntax + Example)

HomeVBA Functions LIST (Category Wise)How to use the 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 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 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 the cell B1.

Notes

  • REPLACE is not case sensitive function.

Related Functions

FORMAT | INSTR | INSTRREV | LCASE | LEFT | LEN | LTRIM | MID | RIGHT | RTRIM | SPACE | STRCOMP | STRCONV | STRING | STRREVERSE | TRIM | UCASE