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()
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
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