VBA-Excel: String Functions – Replace()


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


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

  • 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

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.


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


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.



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

11 thoughts on “VBA-Excel: String Functions – Replace()”

  1. How to replace a string in a sentence?
    E.g. ” 120 AND @DATACOL(1) 120 AND ) 120 AND @DATACOL(” & i & “) 120 AND @DATACOL(” & i & “) < 300"
    Thank you

    • Hello Dave,

      Could you please be more specific like in a sentence, which string you want to replace and with what string.

      You can put it this way

      strNewReplacedStr = Replace(“Your Sentence”, “String you want to replace”, “String with which you want to replace”)

      OldStr = “. ” 120 AND @DATACOL(1) 120 AND ) 120 AND @DATACOL(“ & i & ”) 120 AND @DATACOL(“ & i & ”) < 300″"

      NewStr = Replace(OldStr,"120","100")

      So in your newStr , all the 120 will be replaced by 100

      Hope this might be the solution you were looking for 🙂


  2. Hallo,

    I need to replace a complete string if searched Text is found within it.
    For example

    Input_1 = “Some_Information First_ABC_Text_TEST”
    Input_2 = “Other_Information Second_ABC_Text_TEST”

    ‘in input1 there are two strings in one row: Some_Information and First_ABC…”

    Search Text = “ABC”
    Replace With = “KKK”

    Output_1 = “Some_Information KKK”
    Output_2 = “Other_Information KKK”

    ‘ the complete second string is being replaced by KKK

    I can only get:

    “Some_Information First_WWW_Text_TEST”

    which is not what i need.


    • Try This

      Input_1 = “Some_Information First_ABC_Text_TEST”
      SearchText = “ABC”
      ReplacedText = “KKK”
      StrResult = “”
      Input_1 = Trim(Input_1)
      arrTemp = Split(Input_1, ” “)
      For i = 0 To UBound(arrTemp)
      If InStr(1, arrTemp(i), SearchText, vbTextCompare) > 0 Then
      arrTemp(i) = ReplacedText
      End If
      StrResult = StrResult & ” ” & arrTemp(i)

      MsgBox Trim(StrResult)

      Let me know if this was not what you were looking for 🙂

  3. I have a related question about how to save replaced text in the macro script itself.
    In order to use a certain macro, I have to Unprotect (and later to Protect) a WorkSheet. I don’t want other users to know the Password, so I put the value in the macro (Password = “XYZ”). So far so good.
    What I am looking for is a way to change the password (into “ABC”) and at the same time replace the string “XYZ” into “ABC” so the macro reads Password = “ABC” the next time it is used with the new Password.
    Please advise! Thanks!

      • In my VBAProject I have 2 Modules with a number of Private Sub’s, that users can execute (using buttons).
        In the code I have to Unprotect and later Protect worksheets, e.g. with the following code:
        Password = “CurrentPassword”
        ActiveSheet.Unprotect Password
        (make some changes)
        ActiveSheet.Protect Password

        So the value of Password is a fixed string.

        Please note: I’m NOT referring to the VBA-Project protection, but the individual Worksheets (which passwords are not identical).

        Now I want to change the value of the (Worksheet) password from time to time, without having to use Find/Replace in VBA-editor manually.
        So when I change the Password and set the new protection for a worksheet, I would like to replace the fixed string in the VBA-code from:
        Password = “CurrentPassword”
        Password = “NewPassword”
        automatically; and in both Modules.

        That tric is still a mystery to me…
        (not being familiar with programming)

        If you want to have further explanation, please indicate.

  4. @sef: u will have to store the password some where, its like your gmail password is stored at server. one correction in the code, change in last few lines, chanfe “xlHidden” to “xlVeryHidden”. i have modified the code in the post, u can refer that as well.


    • Tnx again; allready did that. Plus made the row hidden and the worksheet protected (of course the specific cell not blocked for changing the password with the msgboxes).
      Works fine!


Leave a Comment