How to Use the Substitute Function in Excel VBA: 3 Methods

Method 1 – Removing the First Character(s) from a String with the Substitute Function in Excel VBA

Below is a dataset with the names and IDs of employees.

Steps:

• Develop a VBA code using the Substitute function to remove the first two characters (SR) from all the IDs.
• Find out the first 2 characters from the original string. We’ll execute this using the Left function of VBA.

• Use the Substitute function to replace the first 2 characters with an empty string (“”).

• The complete VBA code will be:

⧭ VBA Code:

Sub Remove_First_Characters_with_Substitute()

For i = 1 To Selection.Rows.Count

    For j = 1 To Selection.Columns.Count

        Original_String = Selection.Cells(i, j)

        First_Two_Characters = Left(Original_String, 2)

        Output_String = Application.WorksheetFunction.Substitute(Original_String, First_Two_Characters, "", 1)

        Selection.Cells(i, j) = Output_String

    Next j

Next i

End Sub

• Select the employee IDs and run this Macro (Remove_First_Characters_with_Substitute).

• Remove the first 2 characters from all the IDs.

Method 2 – Replacing the First Character of a String with the Uppercase Letter with the Substitute Function in Excel VBA

There’s another column added to the data set containing the employees’ home country, but all the letters of the country names have been made with lower cases.

Steps:

• Develop a VBA code with the Substitute function to replace the first letters of the country names with upper cases.
• Extract the first letter of the country names using the Left function of VBA.

• Bring out the upper case version of the first letter using the UCase function of VBA.

• Use the Substitute function to replace the first letter of the country name with the uppercase letter.

• The complete VBA code will be:

⧭ VBA Code:

Sub Replace_First_Letter_with_Uppercase()

For i = 1 To Selection.Rows.Count

    For j = 1 To Selection.Columns.Count

        Country_Name = Selection.Cells(i, j)

        First_Letter = Left(Country_Name, 1)

        UpperCase_Letter = UCase(First_Letter)

        Output = Application.WorksheetFunction.Substitute(Country_Name, First_Letter, UpperCase_Letter, 1)

        Selection.Cells(i, j) = Output

    Next j

Next i

End Sub

• Select the home countries and run this Macro (Replace_First_Letter_with_Uppercase).

• Get the first letters of the country names converted to upper case letters.

Method 3 – Replacing a Specific Text with Another Text within a String with the Substitute Function in Excel VBA

The below dataset has the Email Addresses of the employees in a new column.

Steps:

• Develop a VBA code using the Substitute function to convert the Gmail addresses into Outlook addresses.
• Use the Substitute function to replace the string “gmail” of each Email address with the string “outlook”.

• The complete VBA code will be:

⧭ VBA Code:

Sub Replace_Gmail_with_Outlook()

For i = 1 To Selection.Rows.Count

    For j = 1 To Selection.Columns.Count

        Old_Email = Selection.Cells(i, j)

        New_Email = Application.WorksheetFunction.Substitute(Old_Email, "gmail", "outlook")

        Selection.Cells(i, j) = New_Email

    Next j

Next i

End Sub

• Select the Email Addresses and run this Macro (Replace_Gmail_with_Outlook).

• Convert the Gmail addresses into Outlook addresses.

Download the Practice Workbook

Download this workbook to practice.

Further Readings

• How to Find and Replace Using Formula in Excel (4 Examples)
• Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)
• [Fixed!] Excel Find and Replace Not Working (6 Solutions)
• How to Find and Replace Multiple Values in Excel with VBA (3 Examples)
• Add Text and Formula in the Same Cell in Excel (4 Examples)