VBA SubString

Excel VBA SubString

SubString is a part of the string or portion or the character of the string is called “SubString”.There are three types of substring function in VBA LEFT, RIGHT and MID they are similar to the worksheet substrings in excel.

A string is nothing but a series of characters, and characters could be alphabets, numbers, special characters, and also combine all of these.

Often times 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 be used, 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.” This is called as SubString of the string in Excel VBA. To deal with these strings, we have built-in functions under the TEXT function in excel category.

In this article, we will discuss how to get the substring from the full string in VBAString In VBAString functions in VBA do not replace the string; instead, this function creates a new string. There are numerous string functions in VBA, all of which are classified as string or text functions.read more.

VBA SubString

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA SubString (wallstreetmojo.com)

How to Use SubString Functions 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 excelMID In ExcelThe mid function in Excel is a text function that finds strings and returns them from any mid-part of the spreadsheet. read more. 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. Read on the below examples to understand them.

You can download this VBA SubString Excel Template here – VBA SubString Excel Template

Example #1 – Using Left Function

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

Step 1: Create a macro name and define two variable as String.

Code:

Sub SubString_Example1()

  Dim FullName As String
  Dim FirstName As String

End Sub
SubString VBA Example 1

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
SubString VBA Example 1-1

Step 3: Now, the variable FullName holds the value of “Sachin Tendulkar.” Now 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
SubString VBA Example 1-2

Step 4: VBA VBA Excel VBA Left is an inbuilt worksheet text function that facilitates the user to extract a certain number of characters (denoted as N) from the left side of a string. It helps in pulling the leftmost substring from a particular string in the data set.read moreLEFT function 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,” which is 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
SubString VBA Example 1-3

Step 5: The next argument is how many characters we need from the string we have supplied, 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
SubString VBA Example 1-4

Step 6: Now show the result in a message box in VBAMessage Box In VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

Code:

Sub SubString_Example1()

  Dim FullName As String
  Dim FirstName As String

  FullName = "Sachin Tendulkar"
  FirstName = Left(FullName, 6)

  MsgBox FirstName

End Sub
SubString VBA Example 1-5

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

SubString VBA Example 1-6

Example #2 – Get SubString from the Right

Like how we have extracted the substring from the left similarly, we can extract from the right as well. 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
SubString VBA Example 2

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
SubString VBA Example 2-1

Step 3: Now, for the variable LastName, assign the value through the RIGHT excelRIGHT ExcelRight function is a text function which gives the number of characters from the end from the string which is from right to left. For example, if we use this function as =RIGHT ( “ANAND”,2) this will give us ND as the result.read more function.

Code:

Sub SubString_Example2()

  Dim FullName As String
  Dim LastName As String

  FullName = "Sachin Tendulkar"
  LastName = Right(

End Sub
SubString VBA Example 2-2

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
SubString VBA Example 2-3

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
SubString VBA Example 2-4

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
SubString VBA Example 2-5

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

SubString VBA Example 2-6

Example #3 – Using Instr Function

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

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 letter, “a” is in the second position. So we will get 2 as a result in the message box.

Example 3

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 below name I have in the excel sheet.

Example 3-1

Using LEFT, RIGHT, and Instr function, we can extract the substrings. 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.

Example 3-2

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, and we will see the last name in the message box.

Example 3-3

I have assigned the macro buttonThe Macro ButtonA Macro is nothing but a line of code to instruct the excel to do a specific task. Once the code is assigned to a button control through VBE you can execute the same task any time in the workbook. By just clicking on the button we can execute hundreds of line, it also automates the complicated Report.read more to the worksheet, download the workbook, and use them.

Example 3-4

Recommended Articles

This has been a guide to VBA Substring. Here we learn how to extract substring using Left, Right & Instr VBA function along with examples and a downloadable excel template. Below you can find some useful excel VBA articles –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>