×

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!


Related Topics

VBA ActiveSheet

What is VBA Activesheet Property? The active sheet means the current worksheet which you are working on and viewing. The ActiveSheet object signifies the worksheet tab that is selected before running the VBA...

5 minutes read.

VBA Type Mismatch Error

What is a Type Mismatch Error? VBA Type Mismatch Error is a run time error in excel, which often occurs when the data types contained in a VBA code are not matched...

3 minutes read.

Excel VBA Fix Function

VBA Fix Function: The Fix function in VBA truncates the given number to an integer and returns the rounded off integer number. This function both positive and negative numbers to zero. Syntax Fix...

1 minute read.

Excel VBA Day Function

The function Day in VBA returns the day number (from 1 to 31) for the given date value. Syntax Day (Date) Parameter Date (required) – This parameter represents the date. Return This function returns the day...

1 minute read.

Excel VBA Filter Function

VBA Filter Function: The Filter function in VBA returns a subset for the given string array, based on specified criteria. Syntax Filter (SourceArray, Match, [Include], [Compare]) Parameter SourceArray (required) – This parameter the array of Strings that you...

2 minutes read.

Excel VBA Val Function

VBA Val Function: The Val function in VBA converts the given string into a numeric value. This function ignores spaces and continues to read the characters after space(s). It stops...

1 minute read.

VBA Dim

What is Dim? DIM or Dimension or Declare in Memory is a keyword that is used in VBA to declare a variable with the different data types (Integer, String, variable, Boolean, Double, etc.)...

7 minutes read.

Excel VBA CInt Function

VBA CInt Function: The VBA Cint function converts the specified expression into an Integer. Syntax Cint (Expression) Parameter Expression (required) – This parameter represents the expression that you want to convert to an Integer wherein...

1 minute read.

Excel VBA String Function

The String function in VBA creates a String, consisting of several repeated characters. Syntax String (Number, Character) Parameter Number (required) – This parameter represents the number of characters in the returned String. Character (required) – This parameter...

1 minute read.

Excel VBA FormatPercent Function

VBA FormatPercent Function: The FormatPercent function in VBA is used to apply a percent format to a numeric expression, and it returns the result as a string. Syntax FormatPercent (Expression, [NumDigitsAfterDecimal], [IncludeLeadingDigit], [UseParensForNegativeNumbers], [GroupDigits]) Parameter Expression (required)...

2 minutes read.

Miscellaneous Exercise of conditional statements and Loop

We have the already worked with syntax and examples of conditional statements and loops in the previous tutorials. In this tutorial, we will learn how to work with both together.  We will explain...

3 minutes read.

Userform Events

What are Events? Anything you do in to trigger an excel file is an event (an action). Example: If you want a greeting message ‘Good Day’ whenever an excel file is...

7 minutes read.

Excel VBA Trim Function

The Trim function in VBA removes the leading and trailing spaces from the specified string. Syntax Trim (String) Parameter String (required) – This parameter represents the string from which you want to remove the leading and...

1 minute read.

VBA Charts

What is a Chart? A chart is used to visually show numbers or data in a spreadsheet (or spread over multiple spreadsheets) so that the end-user can look at the chart...

3 minutes read.

Excel VBA: Do Until….Loop

DO UNTIL….Loop The “Do Until” Loop is same unlike DO WHILE statement just that it will keep on looping till the condition is not met. This loop is used to repeat a set...

4 minutes read.

Excel VBA IsNull Function

VBA IsNull Function: The IsNull function in VBA returns a Boolean, indicating whether a supplied expression is Null. Syntax IsNull (Expression) Parameter Expression (required)- This parameter represents the name of the argument that you want...

1 minute read.

Excel VBA DateAdd Function

The DateAdd function in VBA adds a time interval to a supplied date and/or time and returns the resultant date/time. Syntax Dateadd (Interval, Number, Date) Parameter Interval (required) – This parameter a string specifying the interval to be...

1 minute read.

Excel VBA MessageBox

Message Box The MsgBox in Excel VBA is a dialog box used to inform the users of your program by showing a custom message or get some necessary inputs such as Yes/No or...

4 minutes read.

Excel VBA CVErr Function

VBA CVErr Function: The CVErr function in VBA returns an Error data type, involving with a user-specified error code. Syntax CVErr (Expression) Parameter Expression (required)- This parameter represents the required error code. Return This function returns an...

1 minute read.

CodeIgniter File Uploading Class

The Codeigniter provides a file Uploading library class which is used to upload any file such as images, pdf, mp3, etc. to the codeigniter’s application. It also allows to set various preferences such...

5 minutes read.