How to Substitute in Excel VBA: Master the Skill in 3 Minutes

Written by Kasper Langmann

Excel VBA, or Visual Basic for Applications, is a powerful tool that can enhance your Excel experience. One of the many functions that you can use in Excel VBA is the Substitute function. This function is used to replace a specific part of a text string with another text string. In this guide, we will explore how to use the Substitute function in Excel VBA.

Understanding the Substitute Function

The Substitute function in Excel VBA is a text function that replaces existing text with new text in a text string. It’s a versatile and useful function that can help you manipulate and manage your data more effectively.

The syntax for the Substitute function is as follows: Substitute(text, old_text, new_text, [instance_num]). The ‘text’ is the original string of text, ‘old_text’ is the text you want to replace, ‘new_text’ is the text you want to replace it with, and ‘instance_num’ is the specific occurrence of the old_text you want to replace. If ‘instance_num’ is omitted, every occurrence of old_text will be replaced with new_text.

How to Use the Substitute Function

Using the Substitute function in Excel VBA is straightforward. Here is a step-by-step guide on how to use it.

Step 1: Open VBA

First, you need to open the VBA editor. You can do this by pressing Alt + F11 on your keyboard. This will open the VBA editor window.

Once the VBA editor is open, you can create a new module by clicking on ‘Insert’ and then ‘Module’. This will create a new module where you can write your VBA code.

Step 2: Write the VBA Code

Next, you need to write your VBA code. In this case, you will be using the Substitute function. Here is an example of how to use the Substitute function in VBA code:

Sub SubstituteExample()

    Dim originalText As String
    Dim newText As String

    originalText = "Hello, World"
    newText = Application.WorksheetFunction.Substitute(originalText, "World", "Excel VBA")

    MsgBox newText

End Sub

In this example, the original text is “Hello, World”. The Substitute function is used to replace “World” with “Excel VBA”. The new text is then displayed in a message box.

Step 3: Run the VBA Code

After writing your VBA code, you can run it by pressing F5 on your keyboard or by clicking on ‘Run’ and then ‘Run Sub/UserForm’. This will execute the VBA code and display the new text in a message box.

Advanced Usage of the Substitute Function

The Substitute function in Excel VBA can be used in more complex ways to manipulate text. Here are a few examples of how you can use the Substitute function in more advanced ways.

Replacing Multiple Text Strings

The Substitute function can be used to replace multiple text strings in a single line of code. For example, you can replace all occurrences of “a” and “e” with “i” in a text string with the following code:

Sub MultipleSubstituteExample()

    Dim originalText As String
    Dim newText As String

    originalText = "Hello, World"
    newText = Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute(originalText, "o", "a"), "e", "i")

    MsgBox newText

End Sub

In this example, the original text is “Hello, World”. The Substitute function is used twice to replace “o” with “a” and “e” with “i”. The new text is then displayed in a message box.

Replacing a Specific Occurrence of a Text String

The Substitute function can also be used to replace a specific occurrence of a text string. For example, you can replace the second occurrence of “l” with “p” in a text string with the following code:

Sub SpecificSubstituteExample()

    Dim originalText As String
    Dim newText As String

    originalText = "Hello, World"
    newText = Application.WorksheetFunction.Substitute(originalText, "l", "p", 2)

    MsgBox newText

End Sub

In this example, the original text is “Hello, World”. The Substitute function is used to replace the second occurrence of “l” with “p”. The new text is then displayed in a message box.

Conclusion

The Substitute function in Excel VBA is a powerful tool that can help you manipulate and manage your text data more effectively. Whether you’re replacing a single text string, multiple text strings, or a specific occurrence of a text string, the Substitute function can handle it all. With a bit of practice, you can master the use of the Substitute function in Excel VBA in no time.