Be the first user to complete this post

  • 0
Add to List

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



Also Read:

  1. VBA-Excel: Arrays – One Dimension, Dynamic Array
  2. VBA-Excel: Date-Time Functions - Timer()
  3. VBA-Excel: Array Functions – Filter()
  4. VBA-Excel: Date-Time Functions – Date(), Now() and Time()
  5. VBA-Excel: Select and Activate Cells - Activate
  6. VBA-Excel: Date-Time Functions – DatePart()
  7. VBA-Excel : Strings Functions – Instr
  8. VBA-Excel: Cells Ranges Offset - Active Cell