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.
Replace(Expression, Find, Replace, [Start], [Count], [Compare])
- 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.
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.
- REPLACE is not case sensitive function.
FORMAT | INSTR | INSTRREV | LCASE | LEFT | LEN | LTRIM | MID | RIGHT | RTRIM | SPACE | STRCOMP | STRCONV | STRING | STRREVERSE | TRIM | UCASE