VBA Function: Replace
The VBA Replace function returns a string after replacing the substring(s) matching the search value.
Usage:
Replace(text, search, replacement)
or
Replace(text, search, replacement, start, limit, compare)
Examples of Usage
Using the Replace function to perform different replacements in a given string:
Sub example()
myText = "www.excel-pratique.com"
'Simple replacement
MsgBox Replace(myText, "excel", "sheets") 'Returns: www.sheets-pratique.com
'Replacement while ignoring the leading characters
MsgBox Replace(myText, "excel", "sheets", 5) 'Returns: sheets-pratique.com
'Replacement with or without defining a limit
MsgBox Replace(myText, "e", "E", 5) 'Returns: ExcEl-pratiquE.com
MsgBox Replace(myText, "e", "E", 5, 1) 'Returns: Excel-pratique.com
'Replacement while considering or ignoring case
MsgBox Replace(myText, "EXCEL", "sheets") 'Returns: www.excel-pratique.com
MsgBox Replace(myText, "EXCEL", "sheets", , , 1) 'Returns: www.sheets-pratique.com
End Sub