Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

VBA-Excel: String Functions – Replace()

Description: 

The Replace() func­tion returns a string after replac­ing a part of string with another sub­string. The no of replace­ments depends on the para­me­ters provided.

Format:

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

Arguments:
  • str­Main­String
    • Manda­tory
    • Type: String
    • The string in which replace­ments to be made.
  • find
    • Manda­tory
    • Type: String
    • Sub­string which needs to be found and replaced in the main string.
  • Replace
    • Manda­tory
    • Type: String
    • Replace­ment substring
  • Start
    • Optional
    • Type: Numeric
    • Start­ing posi­tion in the main string from which the find and replace oper­a­tion will start, if not pro­vided, 1 is assumed.
  • Count
    • Optional
    • Type: Numeric
    • No of replace­ments to be made, if not pro­vided, –1 is assumed which means all the pos­si­ble replace­ments will happen
    • Com­pare
      • Optional
      • Type: Numeric
      • The type of com­par­i­son to find the string in the main string, like vbBina­ryCompare ( Value =0), vbTextCom­pare (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, Com­pare argu­ments are not pro­vided that means all the pos­si­ble replace­ments will happen.

Msg­Box Replace(strMainString, “jain”, “YYYY”, 1, 1)   

Note: Start = 1 and Count =1, means start­ing from posi­tion 1, Count =1 means only one replace­ment will be made.

Replace-2

Msg­Box Replace(strMainString, “sumit”, “AAAA”, 1, 1, vbTextCompare)

Note: Start = 1 and Count =1, means start­ing from posi­tion 1, Count =1 means only one replace­ment will be made and use vbTextCom­pare as comparison

Replace-3

Replace-3

Msg­Box Replace(strMainString, “sumit”, “BBBB”, 15, 1)

Note: Start = 15 and Count =1, means start­ing from posi­tion 15, Count =1 means only one replace­ment will be made and use vbTextCom­pare as comparison.

Replace-4

 

Also Read About  Other String() Functions

INSTR()                       |                         Instr­REV()                       |                        LCase()

UCase()                      |                         Left()                                |                        Right()

LTrim()                        |                         Mid()                                 |                        Trim()

RTrim()                       |                        Replace()                          |                        Space()

Len()                           |                        Str­Comp()                         |                        String()

Happy Macro­ing :)

Sumit Jain

You may also like...

11 Responses

  1. Dave says:

    How to replace a string in a sen­tence?
    E.g. ” 120 AND @DATACOL(1) 120 AND ) 120 AND @DATACOL(“ & i & ”) 120 AND @DATACOL(“ & i & ”) < 300″
    Thank you
    Dave

    • sumitjain says:

      Hello Dave,

      Could you please be more spe­cific like in a sen­tence, which string you want to replace and with what string.

      You can put it this way

      strNewRe­placed­Str = Replace(“Your Sen­tence”, “String you want to replace”, “String with which you want to replace”)

      Old­Str = “. ” 120 AND @DATACOL(1) 120 AND ) 120 AND @DATACOL(“ & i & ”) 120 AND @DATACOL(“ & i & ”) < 300″”

      New­Str = Replace(OldStr,“120”,“100”)

      So in your new­Str , all the 120 will be replaced by 100

      Hope this might be the solu­tion you were look­ing for 🙂

      Sumit

  2. hazy says:

    Hallo,

    I need to replace a com­plete 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 com­plete sec­ond string is being replaced by KKK

    I can only get:

    Some_Information First_WWW_Text_TEST”

    which is not what i need.

    Greet­ings

    • sumitjain says:

      Try This

      Input_1 = “Some_Information First_ABC_Text_TEST“
      Search­Text = “ABC
      Replaced­Text = “KKK
      Str­Re­sult = ““
      Input_1 = Trim(Input_1)
      arrTemp = Split(Input_1, ” ”)
      For i = 0 To UBound(arrTemp)
      If InStr(1, arrTemp(i), Search­Text, vbTextCom­pare) > 0 Then
      arrTemp(i) = Replaced­Text
      End If
      Str­Re­sult = Str­Re­sult & ” ” & arrTemp(i)
      Next

      Msg­Box Trim(StrResult)

      Let me know if this was not what you were look­ing for 🙂

  3. Sef says:

    I have a related ques­tion about how to save replaced text in the macro script itself.
    In order to use a cer­tain macro, I have to Unpro­tect (and later to Pro­tect) a Work­Sheet. I don’t want other users to know the Pass­word, so I put the value in the macro (Pass­word = “XYZ”). So far so good.
    What I am look­ing for is a way to change the pass­word (into “ABC”) and at the same time replace the string “XYZ” into “ABC” so the macro reads Pass­word = “ABC” the next time it is used with the new Pass­word.
    Please advise! Thanks!

    • Sumit Jain says:

      Sorry but Your ques­tion is not clear to me ,
      http://excel-macro.tutorialhorizon.com/password-protection-to-your-macro-code/
      Thats where you can change your pass­word, if this is not what you are ask­ing then please explain in detail

      Sumit

      • Sef says:

        In my VBAPro­ject I have 2 Mod­ules with a num­ber of Pri­vate Sub’s, that users can exe­cute (using but­tons).
        In the code I have to Unpro­tect and later Pro­tect work­sheets, e.g. with the fol­low­ing code:
        Pass­word = “Cur­rent­Pass­word“
        ActiveSheet.Unprotect Pass­word
        (make some changes)
        ActiveSheet.Protect Password

        So the value of Pass­word is a fixed string.

        Please note: I’m NOT refer­ring to the VBA-Project pro­tec­tion, but the indi­vid­ual Work­sheets (which pass­words are not identical).

        Now I want to change the value of the (Work­sheet) pass­word from time to time, with­out hav­ing to use Find/Replace in VBA-editor man­u­ally.
        So when I change the Pass­word and set the new pro­tec­tion for a work­sheet, I would like to replace the fixed string in the VBA-code from:
        Pass­word = “Cur­rent­Pass­word“
        to:
        Pass­word = “New­Pass­word“
        auto­mat­i­cally; and in both Modules.

        That tric is still a mys­tery to me…
        (not being famil­iar with programming)

        If you want to have fur­ther expla­na­tion, please indicate.

  4. Sumit Jain says:

    @sef: u will have to store the pass­word some where, its like your gmail pass­word is stored at server. one cor­rec­tion in the code, change in last few lines, chanfe “xlHid­den” to “xlVery­Hid­den”. i have mod­i­fied the code in the post, u can refer that as well.

    Sumit

    • Sef says:

      Tnx again; all­ready did that. Plus made the row hid­den and the work­sheet pro­tected (of course the spe­cific cell not blocked for chang­ing the pass­word with the msg­boxes).
      Works fine!

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: