VBA Replace Function – Replace String

This tutorial will demonstrate how to use the VBA Replace Function to replace strings of text.

Replace Function

The VBA Replace function replaces a substring of text with another substring.

Sub ReplaceExample_1()
MsgBox Replace("ABCABCABC", "A", "!")       
'Result is: "!BC!BC!BC"
MsgBox Replace("I like pink, red and black", "pink", "purple")
'Result is: "I like purple, red and black"
MsgBox Replace("A, B, C, A, B, C, A, B, C", ", ", ",")
'Result is: "ABCABCABC"

MsgBox Replace("ABCABCABC", "ABC", "!")
'Result is: "!!!"
MsgBox Replace("ABCABCABC", "ABc", "!")
'Result is: "ABCABCABC"
MsgBox Replace("ABCABCABC", "ZBC", "!")
'Result is: "ABCABCABC"

End Sub

Starting Position

By assigning a start position, you can indicate what character position to start with (default = 1).

Sub ReplaceExample_2()
MsgBox Replace("ABCABCABC", "A", "123")        'Result is: "123BC123BC123BC"
MsgBox Replace("ABCABCABC", "A", "123", 2)     'Result is: "BC123BC123BC"
MsgBox Replace("ABCABCABC", "A", "123", 7)     'Result is: "123BC"
MsgBox Replace("ABCABCABC", "A", "123", 8)     'Result is: "BC"

MsgBox Replace("ABCABCABC", "ABC", "!@")        'Result is: "!@!@!@"
MsgBox Replace("ABCABCABC", "ABC", "!@", 2)     'Result is: "BC!@!@"
MsgBox Replace("ABCABCABC", "ABC", "!@", 6)     'Result is: "C!@"
MsgBox Replace("ABCABCABC", "ABC", "!@", 7)     'Result is: "!@"
MsgBox Replace("ABCABCABC", "ABC", "!@", 8)     'Result is: "BC"

End Sub

Replace a Few Occurrences Only

You can also indicate how many instances of the substring to replace (default All)

Sub ReplaceExample_3()
MsgBox Replace("ABCABCABC", "A", "12")      'Result is: "12BC12BC12BC"
MsgBox Replace("ABCABCABC", "A", "12", , 1)     'Result is: "12BCABCABC"
MsgBox Replace("ABCABCABC", "A", "12", , 2)    'Result is: "12BC12BCABC"
MsgBox Replace("ABCABCABC", "A", "12", , 3)    'Result is: "12BC12BC12BC"
MsgBox Replace("ABCABCABC", "A", "12", , 5)    'Result is: "12BC12BC12BC"

MsgBox Replace("ABCABCABC", "A", "12", 3, 1)
'Result is: "C12BCABC"
'We replaced A with 12, 1 time starting from position 3 of the original string.

End Sub

Case Sensitivity

The Replace Function is case sensitive by default. You can switch to case insensitive by adding the optional parameter (vbTextCompare). Here, you must also define the starting position of the search.

Sub ReplaceExample_4()
MsgBox Replace("ABcABCABc", "ABc", "12")
'Result is: "12ABC12"
MsgBox Replace("ABcABCABc", "ABc", "12", , , vbTextCompare)
'Result is: "121212"
'When we use vbTextCompare we need to add the 2 other optional arguments:
'start and count

MsgBox Replace("ABcABCABcABc", "ABc", "12", 3, 1)
'Result is: "cABC12ABc"
'Started from position3 and replaced ABC only 1 time.

End Sub

You can also perform a case-insensitive Replace, by adding Option Compare Text to the top of your module:

Option Compare Text

 

Double Quotes

The Replace Function can replace the double quotes character used to delimit the start and end of a string.

VBA Chr function can return a character from its number in the character set.

MsgBox Chr(34) 'Result is: "

Or

MsgBox Chr(64) 'Result is: @

Double quotes can be used inside the Replace Function using “””” or VBA Function Chr(34).

Sub ReplaceExample_5()
Dim StrEx As String

StrEx = "AB""AB"""
MsgBox StrEx 'Result is: AB"AB"

MsgBox Replace(StrEx, Chr(34), "12")
'Result is: AB12AB12

MsgBox Replace(StrEx, """", "DQ")
'Result is: "ABDQABDQ"

End Sub

Replace Break Line in Cell

The Replace Function can find the break line special character in a cell and remove it or replace it with a space character. The break line special character can be entered in a cell using the keyboard shortcut Alt+Enter and can be used in VBA code with its Character set number using VBA function Chr(10).

Sub ReplaceExample_6()
Dim StrEx As String 'Define a string variable

'Read the value of cell A2 in worksheet Sheet1
StrEx = ThisWorkbook.Worksheets("Sheet1").Range("A2").Value

'The break line character entered with Alt+Enter is Chr(10) and is invisible.
'This code line replaces that character with space
StrEx = Replace(StrEx, Chr(10), " ")

'Write the replaced value in cell B2 in worksheet Sheet1
ThisWorkbook.Worksheets("Sheet1").Range("B2").Value = StrEx

End Sub