VBA macro to replace string between two delimiters within a string

Excel VBA macro to replace string between two delimiters within a string or text line. This article also explains the usage of Replace function in excel VBA macro.

VBA code:

  1. Sub replaceStringBetweenTwoDelimeters()
    ' Macro to replace string "gethow" with "GetHowStuff" in string "welcome to [gethow]"
    Dim stringBwDels As String, originalString As String, firstDelPos As Integer, secondDelPos As Integer, stringToReplace As String, replacedString As String
    originalString = "welcome to [gethow]"
    stringToReplace = "GetHowStuff"
    firstDelPos = InStr(originalString, "[") ' position of start delimiter
    secondDelPos = InStr(originalString, "]") ' position of end delimiter
    stringBwDels = Mid(originalString, firstDelPos + 1, secondDelPos - firstDelPos - 1) 'extract the string between two delimiters
    replacedString = Replace(originalString, stringBwDels, stringToReplace)
    MsgBox ("replaced string is " + replacedString) ' message shows replaced string
    Close #1 ' closing the file
    End Sub

About the Author

SRINI S

A passionate blogger. Love to share solutions and best practices on wordpress hosting, issues and fixes, excel VBA macros and other apps