VBA Replace Function – Replace String
In this Article
This tutorial will demonstrate how to use the Replace VBA function to replace strings of text.
Replace Function
The VBA Replace function returns a string, with substitutions made. With the use of optional parameters, the new string can begin at the position specified by start and ends at the end of the original string.
VBA Replace Substitute a Substring
The VBA Replace function can be used to substitute any substring found, in every part it is found.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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 |
VBA Replace Starting Position
The VBA Replace function can be used to substitute any substring found, in every part it is found. If we assign a start position then the result would be the part of the original string after that starting point.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 |
VBA Replace a Few Occurrences Only
The VBA Replace function can be used to substitute any substring found, in every part it is found. We can optionally decide how many occurrences should be substituted.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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 |
VBA Replace Case Sensitivity
VBA Replace Function by default is case sensitive. You can change that behavior to case insensitive using an optional parameter (vbTextCompare). In that case, you must also define the starting position of the search.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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:
|
1 |
Option Compare Text |
VBA Replace Double Quotes
VBA 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.
|
1 |
MsgBox Chr(34) 'Result is: " |
Or
|
1 |
MsgBox Chr(64) 'Result is: @ |
Double quotes can be used inside VBA Replace function using “””” or VBA function Chr(34).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 |
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Learn More!!
VBA Replace Break Line in Cell
VBA 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).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 |
