Expand Minimize

WorksheetFunction.Replace Method (Excel)

Replaces part of a text string, based on the number of characters you specify, with a different text string.

expression .Replace(Arg1, Arg2, Arg3, Arg4)

expression A variable that represents a WorksheetFunction object.

Parameters

Name

Required/Optional

Data Type

Description

Arg1

Required

String

Text in which you want to replace some characters.

Arg2

Required

Double

The position of the character in Arg1 that you want to replace with Arg4.

Arg3

Required

Double

The number of characters in Arg1 that you want the Replace method to replace with Arg4.

Arg4

Required

String

Text that will replace characters in Arg1.

Return Value

A String value that represents the new string, after replacement.

This example replaces abcdef with ac-ef and notifies the user during this process.

Sub UseReplace() 
 
 Dim strCurrent As String 
 Dim strReplaced As String 
 
 strCurrent = "abcdef" 
 
 ' Notify user and display current string. 
 MsgBox "The current string is: " & strCurrent 
 
 ' Replace "cd" with "-". 
 strReplaced = Application.WorksheetFunction.Replace _ 
 (Arg1:=strCurrent, Arg2:=3, _ 
 Arg3:=2, Arg4:="-") 
 
 ' Notify user and display replaced string. 
 MsgBox "The replaced string is: " & strReplaced 
 
End Sub
Show:
© 2015 Microsoft