How to Use the Substitute Function in Excel VBA (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

The Substitute function is one of the most useful and widely used functions that we use while working with VBA in Excel. In this article, I’ll show you how you can use the Substitute function in VBA in Excel with proper examples and illustrations.


Substitute Function in Excel VBA (Quick View)

Quick View of the Substitute Function in Excel VBA

This will replace “gmail” with “outlook” in the string “[email protected]and return [email protected]“.


Download Practice Workbook

Download this workbook to exercise while you are reading this article.


Introduction to the Substitute Function in Excel VBA

⧭ Overview

The Substitute function takes a string as the input and replaces a specific text within the string with another text.

For example, Substitute(“[email protected]“, “gmail”, “outlook”) will return [email protected]“.

⧭ Syntax

The syntax of the Substitute function is:

=Substitute (Arg1 As String, Arg2 As String, Arg3 As String, [Arg4] As String)

⧭ Arguments

Arguments Required / Optional Explanation
Arg1 Required The string within which a specific text will be replaced. Can be a string, a number, or even a boolean.
Arg2 Required The text that’ll be replaced within the given string. Can be a string, a number, or even a boolean.
Arg3 Required The text with which the given text will be replaced within the given string. Can be a string, a number, or even a boolean.
Arg4 Optional A number denoting the instance of the Arg2 to be replaced. Must be a number.

Return Value

Returns the given string after replacing the specific texts with the given texts.

⧭ Things to Remember

  • The Substitute function can replace values from not only a string but also from a number or even a boolean.

For example, Substitute (10234, 02, 99) will return 19934.

And Substitute (True, “r”, “u”) will return Tuue.

  • If the text that will be replaced (Arg2) remains more than once in the input string (Arg1), the Arg4 denotes which instance of the Arg2 will be replaced. It’s optional. If you don’t mention it, all the instances of the Arg2 will be replaced.

For example, Substitute (“A is active and B is active”, “active”, “inactive”, 2) will return “A is active and B is inactive”.

But Substitute (“A is active and B is active”, “active”, “inactive”) will return “A is inactive and B is inactive”.

  • The Substitute function is originally an Excel function, not a VBA So each time you use it in VBA, you have to use:
Application.WorksheetFunction.Substitute (....)

3 Examples to Use the Substitute Function in Excel VBA

Here are a few examples to learn how to use the Substitute function in VBA in detail.

1. Remove the First Character(s) from a String with the Substitute Function in Excel VBA

You can use the Substitute function to remove a number of characters from the left of a string.

Just replace the first character (s) with an empty string (“”) using the Substitute function.

Here we’ve got a data set with the names and IDs of some employees of a company.

Data Set to Use the Substitute Function in Excel VBA

Now we’ll develop a VBA code using the Substitute function to remove the first two characters (SR) from all the IDs.

To accomplish this, first, we have to find out the first 2 characters from the original string. We’ll execute this using the Left function of VBA.

First_Two_Characters = Left(Original_String, 2)

Then we’ll use the Substitute function to replace the first 2 characters with an empty string (“”).

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

So 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

⧭ Output:

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

Running Macro with the Substitute Function in Excel VBA

You’ll get the first 2 characters removed from all the IDs.

First Two Characters Removed Using the Substitute Function in Excel VBA

Read More: How to Substitute Multiple Characters in Excel (6 Ways)


2. Replace the First Character of a String with the Uppercase Letter with the Substitute Function in Excel VBA

Now there has been another column added to the data set containing the home country of the employees.

But mistakenly, all the letters of the country names have been made with lower cases.

Now we’ll develop a VBA code with the Substitute function to replace the first letters of the country names with upper cases.

First, we will extract the first letter of the country names using the Left function of VBA.

First_Letter = Left(Country_Name, 1)

Next, we’ll bring out the upper case version of the first letter using the UCase function of VBA.

UpperCase_Letter = UCase(First_Letter)

Finally, we’ll use the Substitute function to replace the first letter of the country name with the uppercase letter.

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

So 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

Code with the Substitute Function in Excel VBA

⧭ Output:

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

Running Macro with the Substitute Function in Excel VBA

You’ll get the first letters of the country names converted to upper case letters.

Read More: How to Find and Replace within Selection in Excel (7 Methods)


Similar Readings


3. Replace a Specific Text with Another Text within a String with the Substitute Function in Excel VBA

Finally, we have the Email Addresses of the employees in a new column.

Data Set to Use the Substitute Function in Excel VBA

But all the Email Addresses are Gmail addresses.

We’ll develop a VBA code using the Substitute function to convert the Gmail addresses into Outlook addresses.

This is simple. We’ll use the Substitute function to replace the string “gmail” of each Email address with the string “outlook”.

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

So 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

Code with the Substitute Function in Excel VBA

⧭ Output:

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

It’ll convert the Gmail addresses into Outlook addresses.

Read More: How to Replace Text in Excel Formula (7 Easy Ways)


Conclusion

Using these methods, you can use the Substitute function to replace a text with another text within a string with VBA in string. Do you have any questions? Feel free to ask us.


Further Readings

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

2 Comments
  1. Hi, is there a formula where I can use the substitute function like in the 3rd option but assuming that more people are going to type their email with @gmail.com so whenever they type that in the column it automatically changes only the gmail.com to outlook.com maintaining their first string which we don’t know yet

    • Hi Raymond,
      Thanks for your question. I think you can do your task easily by following the code below.
      • Right-click on the sheet name containing your dataset and then select the View Code option.
      1
      • Type the following code in the opened window and make sure to adjust the number of Target.Column = 5 according to the column number of the emails.

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Column = 5 Then
      New_Email = Application.WorksheetFunction.Substitute(Target.Value, "gmail", "outlook")
      Target.Value = New_Email
      End If
      End Sub

      2
      • After saving the code, return to your worksheet.
      • Type a random email with @gmail.com
      3
      • Press ENTER.
      In this way, the email will be automatically changed from gmail to outlook.
      4

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo