VBA-Excel: String Functions – Replace()

Description: 

The Replace() function returns a string after replacing a part of string with another substring. The no of replacements depends on the parameters provided.

Format:

Replace(strMainString, find, replace[, start[, count[, compare]]])

Arguments:

• strMainString
  • Mandatory
  • Type: String
  • The string in which replacements to be made.

• find
  • Mandatory
  • Type: String
  • Substring which needs to be found and replaced in the main string.

• Replace
  • Mandatory
  • Type: String
  • Replacement substring

• Start
  • Optional
  • Type: Numeric
  • Starting position in the main string from which the find and replace operation will start, if not provided, 1 is assumed.

• Count
  • Optional
  • Type: Numeric
  • No of replacements to be made, if not provided, -1 is assumed which means all the possible replacements will happen

• Compare
  • Optional
  • Type: Numeric
  • The type of comparison to find the string in the main string, like vbBinaryCompare ( Value =0), vbTextCompare (value=1).

  Function FnReplace()
  
      Dim strMainString
  
      strMainString = "my name is sumit jain, and the name is sumit jain"
  
      MsgBox Replace(strMainString, "sumit", "XXXX")        
  
  End Function

Replace()

Note: Start, Count, Compare arguments are not provided that means all the possible replacements will happen.

MsgBox Replace(strMainString, “jain”, “YYYY”, 1, 1)   

Note: Start = 1 and Count =1, means starting from position 1, Count =1 means only one replacement will be made.

Replace-2

MsgBox Replace(strMainString, “sumit”, “AAAA”, 1, 1, vbTextCompare)

Note: Start = 1 and Count =1, means starting from position 1, Count =1 means only one replacement will be made and use vbTextCompare as comparison

Replace-3

MsgBox Replace(strMainString, “sumit”, “BBBB”, 15, 1)

Note: Start = 15 and Count =1, means starting from position 15, Count =1 means only one replacement will be made and use vbTextCompare as comparison.

Replace-4

Also Read About  Other String() Functions

INSTR()                       |                         InstrREV()                       |                        LCase()

UCase()                      |                         Left()                                |                        Right()

LTrim()                        |                         Mid()                                 |                        Trim()

RTrim()                       |                        Replace()                          |                        Space()

Len()                           |                        StrComp()                         |                        String()

Happy Macro­ing

Sumit Jain