Example 1: vba replace
Sub TestMe()
Debug.Print Replace("a,B,c,d,e", ",", "-")
Debug.Print Replace("Mr XXX", "XXX", "Bond")
Debug.Print Replace("a,b,c,d,e", ",", "-", 3, 2)
End Sub
Example 2: excel vba replace part of a string
Function IfInReplace$(s$, substr1$, replacement$, Optional CaseSensitivity = vbTextCompare)
If InStr(1, s, substr1, CaseSensitivity) Then s = Replace(s, substr1, replacement, , , CaseSensitivity)
IfInReplace = s
End Function
MsgBox IfInReplace("abc123def", "123", "")
Example 3: excel vba replaceall in string
Function TemplateReplace$(template$, ParamArray replacements())
Dim c&, s$, t$, e
s = template
For Each e In replacements
c = c + 1
t = "|%" & c & "|"
If InStrB(e, "~~") Then e = Replace(e, "~~", Chr(34))
If InStrB(s, t) Then s = Replace(s, t, e)
Next
TemplateReplace = s
End Function
Const TEMPLATE = "SELECT * FROM |%1| WHERE (survived = |%2|)"
MsgBox TemplateReplace$(TEMPLATE, "titanic", "~~true~~")