VBA SUBSTITUTE

What is VBA Substitute Function in Excel?

In Excel VBA, the Substitute function is used to replace occurrences of a substring within a string with another substring. While it is like the VBA Replace function, it has some minor changes in its syntax. VBA Substitute can be used not only for strings but also for numbers, as seen in this example.

The given number is substituted with 420 for 696. The modified number is then printed in the Immediate tab.

Syntax

The syntax to call the Substitute function in Excel VBA is:

WorksheetFunction.Substitute(expression, find, replacement, [start], [count], [compare])

Where,

• expression: The string expression containing the text you want to replace.
• find: The substring you want to replace.
• replacement: The substring you want to replace find with.
• [start] (optional): The position within an expression where the search will start. If omitted, the search will begin at the first character.
• [count] (optional): The number of occurrences to replace. If omitted, all occurrences will be replaced.
• [compare] (optional): Specifies the type of string comparison in VBA to use. It can have one of the following values:
  • vbBinaryCompare (0): Performs a binary comparison.
  • vbTextCompare (1): Performs a textual comparison.

The Substitute function returns a string with the specified replacements made.

How to Use VBA Substitute Function?

Follow the steps explained below to see how to use the VBA Substitute String function.

Step 1: In the Excel Workbook, select “Developer tab” on the Excel toolbar. Under that, select “Visual Basic”.

This opens the VBA Editor. In the editor, select the “Insert” option on the VBA toolbar and select “Module” in the drop down.

Step 2: Initialize the name of the subroutine to substitute words from a string.

Step 3: Define a string variable to store the string you want to substitute.

Step 4: Define another string to store the string value after it has been substituted.

Step 5: Call the VBA Substitute string function to change a specific word. Here, “Crazy” is changed to “Calm”.

Step 6: Print the substituted string in a message box and add the information icon by calling the “vbInformation” icon.

Code:

Sub ExampleSubstitute()

    Dim originalString As String

    originalString = “Crazy cats ate the hot dog”

    Dim modifiedString As String

    modifiedString = WorksheetFunction.Substitute(originalString, “Crazy”, “Calm”)

    MsgBox modifiedString, vbInformation

End Sub

Step 7: Run the subroutine by pressing the “F5” button or by clicking the “run” button (green arrow) on the Excel Toolbar.

You’ve successfully learned how to use the VBA Substitute function.

Examples

Here, you can continue to see more interesting examples on how to use the VBA Substitute function.

Example #1

Given a table containing Employee data, you need to remove the first letter from the employee ID number.

You can’t just manually erase all the ‘E’s if it is a large dataset. In this case, you can use the VBA Substitute function to replace ‘E’ with an empty string (“”).

Step 1: Declare a sub-procedure to remove the first character from a string value.

Step 2: Set the worksheet in which this sub-procedure will work in.

Step 3: Find the last row of the table using the xlUp function. This is used to find the last non-empty row in the table.

Step 4: Initialize a FOR-loop to run throughout the table.

Step 5: Assign the string value to be substituted to a string variable. Here, the values are in the second column.

Step 6: Using the VBA Left function, get the first character by selecting only one character from the left.

Step 7: Substitute the first character with an empty string and initialize vbTextCompare. With this, all the ‘E’s are removed from the ID number.

Step 8: Keep the modified string on the same row, substituting it with the original value.

Step 9: Continue the FOR-loop.

Code:

Sub RemoveTheFirstCharacter()

    Set ws = ThisWorkbook.Worksheets(“Sheet2”)

    LastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row

    For i = 2 To LastRow

        original_string = Cells(i, 2)

        First_char = Left(original_string, 1)

        op_string = WorksheetFunction.Substitute(original_string, First_char, “”, 1)

        Cells(i, 2) = op_string

    Next i

End Sub

Step 10: Run the above sub-procedure and then go back to the worksheet where the employee details are.

Example #2

Suppose you have a table where all the string values are in lowercase. You want to convert only the first letter of the word to uppercase. You can use the VBA Substitute function.

Here, you can use a FOR-loop to run through the table and convert only the first word from lowercase to uppercase.

Step 1: Start this process by naming the subroutine to convert the first word to uppercase.

Step 2: Call a FOR-loop through the column to be capitalized. Here the loop runs from cell “G2” to cell “G10”.

Step 3: Take the string values to be substituted and store them in a string variable.

Step 4: Get the first word of a string by using the VBA Left function and get the word one word to the left. It takes only the first word.

Step 5: Convert the first letter to uppercase using the VBA UCase function.

Step 6: Call the VBA Substitute string function and substitute the first letter with the letter turned to uppercase and stored in a string.

Step 7: Set the substituted value on the cell.

Step 8: Continue the FOR-loop for all the other cells.

Code:

Sub SubstituteWithUpperCase()

    For Each i In Range(“G2:G10”)

        country_string = i

        First_char = Left(country_string, 1)

        Upper_case = UCase(First_char)

        output_string = WorksheetFunction.Substitute(country_string, First_char, Upper_case, 1)

        i = output_string

    Next i

End Sub

Step 9: Click the green arrow button when you want to run the code. Then, go back to the worksheet and look at the table.

Example #3

Consider that you have a string. You want to substitute a specific word from a sentence. It can be done using the Substitute function in Excel VBA.

Step 1: Begin by declaring a new subroutine for your purposes.

Step 2: Define a string variable to store the original sentence.

Step 3: Assign the string value to the string variable initialized earlier.

Step 4: Define a new string variable to store the modified string value after you have called the VBA Substitute string function.

Step 5: Substitute the word of your choice in quotes with another string as shown.

Here, the word “gun” is changed to “tranqulizer.”

Step 6: Print the modified string and define the title of the message box after defining the icon “vbInformation.”

Code:

Sub StringSubstitute()

    Dim origString As String

    origString = “I shot an elephant with a gun”

    Dim modString As String

    modString = WorksheetFunction.Substitute(origString, “gun”, “tranquilizer”)

    MsgBox modString, vbInformation, “String substitution”

End Sub

Step 7: Run the subroutine to see the result.

The modified sentence is printed in a message box in VBA as shown above.

Important Things To Note

• The VBA WorksheetFunction.Substitute is excellent for simple text replacements within strings.
• If you need to perform the same substitution in multiple places, Substitute can save you time by handling them all at once.
• While Substitute is helpful for essential replacements, if your requirements involve complex string manipulations, consider other approaches or functions.
• By default, VBA Substitute is case-sensitive. Be mindful of this if you need case-insensitive replacements.
• Ensure that you’re aware of any special characters in your strings, as they may behave unexpectedly when using Substitute.

Frequently Asked Questions (FAQs)

Recommended Articles

Guide to VBA Substitute Function in excel. Here we explain how to use VBA Substitute along with examples & downloadable excel template. You can learn more from the following articles –

• VBA Long
• VBA ISNULL
• VBA With Statement