Excel VBA Replace Function
2020-01-30 09:13:07

Excel VBA Replace Function

VBA Replace Function: The Replace function in VBA searches for a substring within the specified string and replaces its occurrences with a second substring.

Syntax

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

Parameter

Expression (required) – This parameter represents string that you want to search.

Find (required) – It represents the substring that you want to find and replace.

Replace (required) – This parameter represents a substring that you want to replace with the Find substring. The default value is 1.

Start (optional) – This parameter represents the position within the Expression that the string should start at.

Count (optional) - It signifies the number of occurrences of the Find substring that you want to replace. The default value is -1.  

Compare (optional) - This parameter represents the type of comparison to make. The default value is vbBinaryCompare.

It can take any of the following values:

  • vbBinaryCompare (default value): To perform a binary comparison
  • vbTextCompare: To perform a text comparison
  • vbDatabaseCompare: To perform a database comparison

Return

This function returns a string after replacing the specified substring with another given substring.

Example 1

Sub ReplaceFunction_Example1()
 ' Replace all occurrences of string "Excel" with "VBA".
 Dim Str1 As String
 Dim Str2 As String
 Str1 = "Hello Excel. Welcome to Excel world! "
 Str2 = Replace(Str1, "Excel", "VBA")
 'it will return string "Hello VBA. Welcome to VBA world! "
 ActiveCell.Value = Str2
 End Sub

Output

Hello VBA. Welcome to VBA world!

Example 2

Sub ReplaceFunction_Example1()
 'Starting from position 30 of specified expression,
 ' Replace all occurrences of string "Excel" with new string "VBA".
 Dim Str1 As String
 Dim Str2 As String
 Str1 = "Hello Excel. Welcome to Excel world! "
 Str2 = Replace(Str1, "Excel", "VBA", 20)
 'it will return string "e to VBA world!" 
 ActiveCell.Value = Str2
 End Sub

Output

e to VBA world!

Example 3

Sub ReplaceFunction_Example1()
 ' Replace all occurrences of string "Excel" withc "".
 Dim Str1 As String
 Dim Str2 As String
 Str1 = "Hello Excel. Welcome to Excel world! "
 Str2 = Replace(Str1, "Excel", "")
 'it will return string "Hello . Welcome to  world!"
 ActiveCell.Value = Str2
 End Sub

Output

Hello . Welcome to world!