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
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()
Happy Macroing
Sumit Jain
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
Dave
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 🙂
Sumit
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.
Greetings
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)
Next
MsgBox Trim(StrResult)
Let me know if this was not what you were looking for 🙂
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!
Sorry but Your question is not clear to me ,
http://excel-macro.tutorialhorizon.com/password-protection-to-your-macro-code/
Thats where you can change your password, if this is not what you are asking then please explain in detail
Sumit
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”
to:
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.
http://excel-macro.tutorialhorizon.com/excel-vba-change-passwords-for-all-the-worksheets-in-one-shot/
i hope this is what you were looking for
Sumit Jain
I was looking for a solution without having to store the password somewhere in a worksheet.cell (keep it in the macro)…
But this will do off course.
Tnx Sumit, for your appreciated effort!
@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.
Sumit
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!