VBA SubString

A SubString is a part of the string or portion, or the character of the string is called “SubString.” There are three types of SubString functions in VBA: LEFT, RIGHT, and MID. They are similar to the worksheet substrings in Excel.

A string is nothing but a series of characters. The characters could be alphabets, numbers, special characters, and combinations.

Often in Excel, when we work with data, which is the string, we need to get only the portion of the string to facilitate our purpose. We may not need the full string to use, but we need only the part of the string for our use. For example, if you have the name “Sachin Tendulkar,” you may need only the first part of the name, i.e., only “Sachin.” It is known as the SubString of the string in Excel VBA. We have built-in functions under the TEXT function in the excel category to deal with these strings.

This article will discuss getting the substring from the full string in VBA.

To extract the substring from the string, we have some of the built-in text functions, and some of the important functions are LEFT, RIGHT, INSTR and MID in excel. The function Instr will serve as the supporting function for the other three functions.

We will see how to use these functions to extract the substrings practically. Please read the below examples to understand them.

Example #1 - Using Left Function

If you have the full name as “Sachin Tendulkar” and need only the first name extracted as the substring, use the following code to get the same.

Step 1: Create a macro name and define two variables as a string.

Code:

Sub SubString_Example1()  Dim FullName As String  Dim FirstName As String End Sub

Step 2: Now, assign the name “Sachin Tendulkar” to the variable FullName.

Code:

Sub SubString_Example1()  Dim FullName As String  Dim FirstName As String  FullName = "Sachin Tendulkar" End Sub

Step 3: The variable FullName holds the value of “Sachin Tendulkar.” We need to extract the Excel VBA substring of the first name from the full name. So, assign the value for the variable FirstName through the LEFT function.

Code:

Sub SubString_Example1()  Dim FullName As String  Dim FirstName As String  FullName = "Sachin Tendulkar"  FirstName = Left( End Sub

Step 4: The VBA LEFT function's first argument is String. That is what is the full value or full string. In this example, our full value or string is "Sachin Tendulkar," assigned to the variable FullName.

So supply variable FullName as the argument.

Code:

Sub SubString_Example1()  Dim FullName As String  Dim FirstName As String  FullName = "Sachin Tendulkar"  FirstName = Left End Sub

Step 5: The next argument is how many characters we need from the supplied string. So, in this case, we need the first name "Sachin," so totally, we need 6 characters from the left side.

Code:

Sub SubString_Example1()  Dim FullName As String  Dim FirstName As String  FullName = "Sachin Tendulkar"  FirstName = Left(FullName, 6) End Sub

Step 6: Now, show the result in a message box in VBA.

Code:

Sub SubString_Example1()  Dim FullName As String  Dim FirstName As String  FullName = "Sachin Tendulkar"  FirstName = Left(FullName, 6)  MsgBox FirstName End Sub

Step 7: Run the macro and see the first name as a substring in the message box.

Example #2 - Get SubString from the Right

Like how we have extracted the substring from the left similarly, we can also extract it from the right. Again, take the same name as an example.

Step 1: Define two variables as String.

Code:

Sub SubString_Example2()  Dim FullName As String  Dim LastName As String End Sub

Step 2: As usual, assign the value to the variable FullName as “Sachin Tendulkar.”

Code:

Sub SubString_Example2()  Dim FullName As String  Dim LastName As String  FullName = "Sachin Tendulkar" End Sub

Step 3: Now, for the variable LastName, assign the value through the RIGHT excel function.

Code:

Sub SubString_Example2()  Dim FullName As String  Dim LastName As String  FullName = "Sachin Tendulkar"  LastName = Right( End Sub

Step 4: String is our FullName, so supply the variable.

Code:

Sub SubString_Example2()  Dim FullName As String  Dim LastName As String  FullName = "Sachin Tendulkar"  LastName = Right(FullName, End Sub

Step 5: Length is how many characters we need from the right side. We need 9 characters on the right side.

Code:

Sub SubString_Example2()  Dim FullName As String  Dim LastName As String  FullName = "Sachin Tendulkar"  LastName = Right(FullName, 9) End Sub

Step 6: Show this value in the message box.

Code:

Sub SubString_Example2()  Dim FullName As String  Dim LastName As String  FullName = "Sachin Tendulkar"  LastName = Right(FullName, 9)  MsgBox LastName End Sub

Step 7: Run the macro. We will see the last name in the message box.

Example #3 - Using Instr Function

In the above examples, we had only one name. So, we have directly supplied how many characters we need from the left and right. But in the case of many names, first name and name characters are not the same. It will differ from name to name. So, in those cases, we cannot supply the number of characters directly so that we can use the function Instr.

The Instr function will return the supplied character position in the string. For example, look at the below code.

Code:

Sub SubString_Example3()  Dim Position As String  Position = InStr(1, "Sachin", "a")  MsgBox Position End Sub

InStr(1, "Sachin," "a"), this will identify the position of the letter "a" as the first appearance in the string "Sachin." In this case, the letter "a" is in the second position. So, we will get 2 as a result in the message box.

Like this, we can use the Instr function to find the space character between the first name and last name.

For example, look at the name below in the Excel sheet.

We can extract the substrings using the LEFT, RIGHT, and Instr functions. For example, below is the code to extract the first name.

Code:

Sub FirstName()  Dim K As Long  Dim LR As Long  LR = Cells(Rows.Count, 1).End(xIUp).Row For K = 2 To LR Cells(K, 2).Value = Left(Cells(K, 1).Value, InStr(1, Cells(K, 1).Value, "") - 1) Next K End Sub

Run the macro and see the first name as a substring in the message box.

Use the below code to extract the last name as a substring.

Code:

Sub LastName()  Dim K As Long  Dim LR As Long  LR = Cells(Rows.Count, 1).End(xIUp).Row  For K = 2 To LR   Cells(K, 3).Value = Right(Cells(K, 1).Value, Len(Cells(K, 1)) - InStr(1, Cells(K, 1).Value, ""))  Next K End Sub

Run the macro. We will see the last name in the message box.

We have assigned the macro button to the worksheet. Download the workbook and use them.