vba substitute string code example

Example 1: vba replace

' Replace into string
Sub TestMe()
    Debug.Print Replace("a,B,c,d,e", ",", "-")         'a,B,c,d,e -> a-B-c-d-e
    Debug.Print Replace("Mr XXX", "XXX", "Bond")       'Mr XXX -> Mr Bond
    Debug.Print Replace("a,b,c,d,e", ",", "-", 3, 2)   'a,B,c,d,e -> b-c-d,e
End Sub

Example 2: excel vba replace part of a string

'VBA function to replace a substring... if it exists in the full
'string. Checking to see if it exists is very quick. Replacing is
'slow by comparison. So it is wise to check first:

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", "")    '<--displays: abcdef

Example 3: excel vba replaceall in string

'VBA function to make multiple replacements in a template 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~~")

'Displays: SELECT * FROM titanic WHERE (survived = "true")