Excel VBA Replace: How to Automatically Replace Cells in Your Spreadsheet

While  working with MS Excel , sometimes you may need to delete values in strings or change them. You can automate that by using the powerful Replace() which is available in Excel VBA (Visual Basic for Applications). Today, we walk you through the series of steps to understand and master the Replace() function. The prerequisites for this intermediate course are the basic knowledge of Excel (here’s a course that can give you an introduction to Excel 2o13), strings and preliminary understanding of Excel VBA. If not, we recommend that you go through this beginner’s course on Excel VBA and macros. For a quick refresher, you can do a quick read through of our VBA tutorial.

 Excel VBA Replace() is a simple yet very useful string function. As the name suggests, Replace() is used to replace a set of characters in a string with a new set of characters. The basic syntax of a VBA Replace function looks like this:

Replace(Source_string, Old_string, Replacement_string, [start, [count, [compare]]] )

Let’s go through each parameter to understand them better.

• Source_string: This is the complete source string, of which you want some characters to be replaced.
• Old_string: It’s the string which is to be replaced, ie the subset of source_string that you want to replace
• Replacement_string: Is the string or a set of characters with which you want “Old_string” is to be replaced.
• Start: Stands for the numerical position in the “Source_string” from which the search should start. This is an optional parameter. If this parameter is omitted, then by default the search begins at position 1.
• Count: This parameter stands for the frequency of occurrences of Old_string to be replaced. Like “start”, it’s an optional parameter. If this argument is omitted, then each occurrence of “Old_string” in the “Source_string” will be replaced.
• Compare: This is also an optional parameter. It represents the type of comparison algorithm to be used while the Replace Function searches for the occurrences of “Old_string” in the “Source_string.” Here are your options:

1. vbBinaryCompare is the parameter value for binary comparison.
2. vbTextCompare is the argument for textual comparison.
3. Finally, the parameter value vbDatabaseCompare does a comparison based on information in your database.

Now that we’re familiar with the syntax of Replace function, lets move on to a few simple practical examples.

Examples

Replace (“Thank You", "You", "Everybody")

This example will return, “Thank Everybody”.

Replace ("Software Program", "Unique","code")

Guess what this will return? The code will return “Software Program.” The reason is we have asked the Replace function to replace “Unique.” However, you can see that “Unique” text string is not present inside the source string. So, Replace will leave the source string unchanged.

Replace ("Animal", "a", "f",2)

This code will return, “Animfl.” The reason is in the Replace function code, the search for character “a” starts from the second position. Wherever, “a” is found, it is replaced with “f”.

Replace ("Animal", "a", "f",1,1)

Here the Replace() will return “fnimal”.  The reason being we have instructed the VBA Replace statement to replace only one occurrence of “a” with “f”.

We suggest you work out these examples and learn a bit more about VBA macros (you can use this VBA course) before moving on to more complex programs using Replace function. Here on, we will use the Visual Basic Editor to write the code. We assume that you know how to save, compile and run programs on this editor. You can always look up our course on Excel VBA and Macros here.

How  to Remove Space from a String

Sub removeSpace()
Dim stringSpace As String
stringSpace = " this string contains spaces "
stringSpace = Replace(stringSpace, " ", "")
End Sub

Here we have declared stringSpace to be a variable of type string. It is initialized to a string which contains spaces. Replace() has the ‘stringSpace’ to the be source string. Every occurrence of space in the source string is removed using VBA Replace statement. Finally, stringSpace contains “thisstringcontainsspaces” which is the end result.

How to Replace a String within Inverted Comma

Sub replaceQuotedString()
Dim y, longString, resultString1 As String
y = Chr(34) & "abc" & Chr(34)
longString = "Let's replace this string: " &y
resultString1 = Replace(longString, y, "abc")
End Sub

Here we declare y, longString, resultString1 as variables of data type string. Chr() converts the numerical values to string data type. In other words, it introduces the quotation marks to the numerical values. And(&) operator concatenates strings. Value of “y” is “34abc34.” The  “longString” value is “Let’s replace this string: 34abc34″. In the Replace() value of source string is  the “longString.” That is “34abc34” is replaced by “abc”.  The resultstring1 now stores the value “Let’s replace this string: abc”

How to Remove Square Brackets from a String using Excel VBA

Sub removeSquareBrackets1()
Dim trialStr As String
trialStr = "[brackets have to be removed]"
trialStr = Replace(trialStr, "[", "")
trialStr = Replace(trialStr, "]", "")
MsgBox (trialStr)
End Sub

In this program, we’ve declared trialStr as a variable of type string.  It’s assigned the value”[brackets have to be removed].” The first occurrence of the replace function removes the left square bracket from trialStr. The variable now contains “brackets have to be removed].” In the second occurrence of the Replace function, the right square bracket is removed. Finally the value of trialStr is “brackets have to be removed.” Note that there are no square brackets in trialStr now. And the MsgBox() displays  the result.

How to Edit a Url Using Replace()

Enter this formula into any cell in your current Excel sheet.

=HYPERLINK("http://www.microsoft.com", "Microsoft Headquarters")

This creates a hyperlinked URL in the active cell of your worksheet. In the VBA editor, write the following code

Sub editURL1()
Dim URL1, NewURL
URL1 = ActiveCell.Formula
NewURL = Replace(URL1, "Microsoft Headquarters", "World Office")
ActiveCell.Formula = NewURL
End Sub

In this program, we’ve declared URL1 and NewURL as variables. URL1 is initialized to the value in the selected cell. Replace () searches for occurrence of “Microsoft Headquarters” in the URL and replaces it with “World Office.”  The selected cell is assigned the value of NewURL.

Programming using Excel VBA is interesting and gives you exposure to powerful functionality and features. Mastering it is important to efficiently and effectively use Excel.  Once you’ve tried these examples for yourself, do share your learning and experience with us. Once you’re ready to take the next step, hop over to this Ultimate VBA course, to check out some advanced VBA concepts.