VBA Replace String

What is Excel VBA Replace String?

Excel VBA (Visual Basic for Applications) provides a Replace function that allows you to replace occurrences of a specific string within a text string. It is possible to implement VBA Replace string in a text file.

Consider the following example:

This example replaces “World” with “universe” in the given string and prints it in the Immediate tab as the output:

Syntax – Replace Function:

Replace(expression, find, replace, [start], [count], [compare])

Where:

expression: This is the text string or a cell reference in excel containing the text where you want to perform the replacement.
find: This is the substring you want to search for within the expression.
replace: This is the substring you want to replace the occurrences of “find” with.
start: (Optional) This parameter indicates the character position within the expression where you want to start.
count: (Optional) This parameter specifies the number of replacements you want to make. If omitted, all occurrences of “find” will be replaced.
compare: (Optional) This parameter determines the type of comparison to be performed. It can take any of the following values:
- vbBinaryCompare: Performs a binary comparison (default).
- vbTextCompare: Performs a textual comparison.

The Replace function returns a new string with the specified replacements made.

What is Excel VBA Replace String?

The VBA Replace String function is used to replace strings in a given text. It basically replaces substrings with another substring.
The syntax of the replace function is:

Replace(expression, find, replace, [start], [count], [compare])

The Replace function is case-sensitive by default (using vbBinaryCompare). If you want case-insensitive replacements, use the vbTextCompare option for the compare parameter.
The Replace function returns a modified string as the result. You need to assign the result back to a variable or the original string to capture the changes.

How to Replace Text in String Using VBA?

To implement the VBA Replace string in text file, we perform the following steps as shown below:

Go to the “Developer” section in the toolbar and click the “Visual Basic” option. Now, the VBA Editor opens to add functions and Sub procedures. Then, click the “Insert” and “Module” buttons to create a new module or blank page.
Initialize a sub-procedure to replace a string.
Accept an input text from the user.
Replace quick to slow using VBA Replace function.
The replace function can be done multiple times as shown below:

Code:

Sub PrintExample()
input_text = “The quick brown fox jumped over the lazy dog.”
‘Replace “quick” to “slow”
input_text = Replace(input_text, “quick”, “slow”)
‘Replace “brown” to “red”
input_text = Replace(input_text, “brown”, “red”)
‘Replace “lazy” to “energetic”
input_text = Replace(input_text, “lazy”, “energetic”)
Debug.Print input_text
End Sub
Print the changed text by pressing F5 or the Run button on the VBA toolbar.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Examples

Let us look at some examples below where we use the REPLACE function to replace strings in some text.

Example #1

Consider an example where we need to implement VBA Replace String in a column by replacing “apples” with “oranges” in an Excel column.

Step 1: Define a sub-procedure that performs VBA Replace string in Range from cells A1-A5.

Step 2: Define two variables, rng, to define the Range of the table, and cell, as an iterative integer to be used in a FOR loop.

Step 3: Assign rng as the range between cells A1-A5.

Step 4: Initialize a For loop that runs throughout the range of the table and checks all values to perform VBA Replace String in cell from Apples to Oranges.

Code:

Sub ReplaceInRangeExample()
Dim rng As Range
Dim cell As Range
‘ Set the range of cells to modify
Set rng = Range(“A1:A5”)
‘ Loop through each cell in the range
For Each cell In rng
‘ Replace “apple” with “orange” in each cell
cell.value = Replace(cell.value, “Apple”, “Orange”)
Next cell
End Sub

Step 5: Run the code for the above table. The output is shown below:

VBA Replace String in Excel - Example 1 - Step 5

Example #2

Consider an example where you must replace a single word that occurs multiple times in a text file. We need to implement VBA Replace String in text file to change all occurrences of the word present in a file.

“I love apples. Apples are delicious.”

Step 1: Initialize a sub-procedure to convert “apples” to “bananas.”

VBA Replace String in Excel - Example 2 - Step 1

Step 2: Declare variables text which accepts input text, searchWord, which is assigned to the word which will be changed, and replaceWord as the word to be replaced using VBA Replace function.

Step 3: Accept an input text as the example file shown above.

Step 4: Assign the word to be searched and the word to be replaced to searchWord and replaceWord, respectively.

Step 5: Check whether the word exists in the input text. If it exists, the VBA Replace function is evoked.

The code checks if the search word (“apples”) is present in the text using the InStr function.
If the search word is found, it then uses the Replace function to replace all occurrences of the search word with the replaced word (“bananas”).
The vbTextCompare parameter is used for case-insensitive comparison.

Step 6: After this, the text after the replacement is printed using Debug.Print

Code:

Sub ReplaceWithConditionExample()
Dim text As String
Dim searchWord As String
Dim replaceWord As String
‘ Input text
text = “I love apples. Apples are delicious.”
‘ Set the search word and replace word
searchWord = “apples”
replaceWord = “bananas”
‘ Check if the search word is present in the text
If InStr(1, text, searchWord, vbTextCompare) > 0 Then
‘ Replace the search word with the replace word
text = Replace(text, searchWord, replaceWord, 1, -1, vbTextCompare)
End If
‘ Display the modified text
Debug.Print text
End Sub

Step 7: Print the above VBA code. The output is printed in the immediate tab.

VBA Replace String in Excel - Example 2 - Step 7

Example #3

Consider an example where VBA Replace String is implemented only under specific conditions.

“I love my dog. My dog is amazing, and so is my parrot.”

Here, we need to change “dog” to “cat” only if the length of the string is greater than 20.

Step 1: Initialize a sub-procedure to convert “dog” to “cat”.

Excel VBA Replace String - Example 3 - Step 1

Step 2: Declare variables text which accepts input text, and replaceWord as the word to be replaced with using VBA Replace function.

Step 3: Accept an input text as the example file shown above.

Step 4: Using an If-Else statement, set the replace function based on a condition.

Step 5: Print the replaced text.

Code:

Sub WithConditionExample()
Dim text As String
Dim replaceWord As String
‘ Input text
text = “I love my dog. My dog is amazing, and so is my parrot”
‘ Set the replace word based on a condition
If Len(text) > 20 Then
replaceWord = “cat”
Else
replaceWord = “dog”
End If
‘ Replace “fox” with the replace word
text = Replace(text, “dog”, replaceWord)
‘ Display the modified text
Debug.Print text
End Sub

Step 6: The output is as shown:

Excel VBA Replace String - Example 3 - Step 6

Important Things to Note

Ensure you provide the correct values for the expression, find, and replace parameters. Double-check the parameter order and data types in VBA to avoid unexpected results.
Before applying the VBA Replace string function to a large dataset or critical data, test it on a smaller sample to verify that it produces the desired results.
The compare parameter allows you to choose between binary comparison (vbBinaryCompare) and textual comparison (vbTextCompare). Choose the appropriate mode based on your specific needs.
The Replace function returns a modified string, so ensure that you assign the result back to a variable or the original string.
If you’re replacing text within a range of cells or a larger dataset, be cautious to avoid accidentally overwriting the original data. Always make backups or work with a copy of the data if necessary.
While Replace is helpful for primary replacements, consider using regular expressions or other string manipulation functions provided by VBA for more complex string operations such as pattern matching or multiple variations.

Frequently Asked Questions (FAQs)

1. How do I replace the nth character in a string in Excel VBA?

To replace the nth character in a string in Excel VBA:
• Convert the string to an array of characters.
• Replace the nth character in the array.
• Join the array back into a string.

2. How to replace a wildcard in a string in Excel VBA?

To replace a wildcard in a string in VBA Replace String:
• Use the Replace function with the wildcard character as the find parameter.
• Provide the replacement string as the replacement parameter.
• Specify any additional optional parameters as needed (such as start position or comparison mode).
• Assign the modified string back to the original string variable or a new variable.

3. Why is Excel VBA Replace String not working?

• Incorrect parameter order or missing arguments.
• Case sensitivity mismatch.
• Unrecognized or unsupported wildcard characters.
• The original string or search string does not match due to hidden characters or formatting.
• The replacement string is not assigned back to the original variable.
• No matches were found for the search string in the original string.

4. Can you replace a string in Excel VBA without using Replace function?

Yes, it is possible to replace a string in Excel VBA without using the Replace function by following these steps:
• Use the InStr function to find the position of the substring you want to replace within the original string.
• Extract the portions of the original string before and after the substring using the Left and Right functions.
• Concatenate the modified portions and the replacement string to form the updated string.

Download Template

This article must be helpful to understand the VBA Replace String, with examples. You can download the template here to use it instantly.

VBA Replace String Download

VBA Replace String