FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

VBA Right Function

Updated on June 12, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Right Function in VBA Excel

The Right function is the same as in the Worksheet function and VBA. The use of this function is that it gives us the substring from a given string, but one can search from right to left of the string. This type of string function in VBA one may use with application.worksheet function method.

The RIGHT function in Excel VBA extracts characters from the right side of the supplied text values. In Excel, we have many text functions to deal with text-based data. Some useful functions are LEN, LEFT, RIGHT, and MID functions to extract characters from the text values. A common example of using these functions is extracting the first and last names separately from the full names.

The RIGHT formula is also there in the worksheet. In VBA, we need to rely on the worksheet function class to access the VBA RIGHT function. Therefore, we also have the built-in RIGHT function in VBA.

Now, look at the syntax below of the VBA RIGHT formula.

VBA Right Formula
  • String: It is our value. We are trying to extract the characters from the right side of the string from this value.
  • Length: From the supplied String, how many characters do we need? If we need four characters from the right side, we can supply the argument as 4.

For example, if the string is “Mobile Phone.” We want to extract only the word “Phone,” so we can supply the argument like the one below.

RIGHT (“Mobile Phone,” 5)

We mentioned 5 because the word “Phone” has 5 letters. In the article’s next section, we will see how we can use it in VBA.

VBA Right Function

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 Right Function (wallstreetmojo.com)

Examples of Excel VBA Right Function

The following are examples of Right function VBA Excel.

You can download this VBA Right Function Excel Template here – VBA Right Function Excel Template

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Example #1

We will show you a simple example to start the proceedings. Assume you have the string “New York,” and if you want to extract 3 characters from the right, follow the below steps to write the code.

Step 1: Declare the variable as VBA String.

Code:

Sub Right_Example1()

  Dim k As String

End Sub
Example 1

Step 2: Now, we will assign the value by applying the RIGHT function for this variable.

Code:

Sub Right_Example1()

  Dim k As String

  k = Right(

End Sub
Example 1-1

Step 3: The first argument is String, and our string for this example is “New York.”

Code:

Sub Right_Example1()

  Dim k As String

  k = Right("New York",

End Sub
Example 1-2

Step 4: Next is how many characters we need from the supplied string. In this example, we need 3 characters.

Code:

Sub Right_Example1()

  Dim k As String

  k = Right("New York", 3)

End Sub
Example 1-3

Step 5: We have 2 arguments to deal with, so we are done. Now, assign the value of this variable in the message box in VBA.

Code:

Sub Right_Example1()

  Dim k As String
  
  k = Right("New York", 3)

  MsgBox k

End Sub
Example 1-4

Run the code using the F5 key or manually and see the result in a message box.

Example 1-5

In the word “New York” from the right side 3 characters are “ork.”

We will change the length from 3 to 4 to get the full value.

Code:

Sub Right_Example1()

  Dim k As String

  k = Right("New York", 4)

  MsgBox k

End Sub
Example 1-6

Run this code manually or using the F5 key. Then, we will get “York.”

VBA Right Example 1-7

Example #2

Now, look at one more example. This time, consider the string value as “Michael Clarke.”

If you supply the length as 6, we will get “Clarke.”

Code:

Sub Right_Example1()

    Dim k As String

    k = Right("Michael Clarke", 6)

    MsgBox k
End Sub
VBA Right Example 2

Run this code using the F5 key or manually to see the result.

VBA Right Example 2-1

Dynamic Right Function in Excel VBA

We have supplied the length argument numbers manually if you observe our previous two examples. But this is not the right process to do the job.

In each of the strings, right-side characters are different in each case. Therefore, we cannot refer to the length of the characters manually for each value differently. It is where the other string function, “Instr,” plays a vital role.

The Instr function returns the supplied character position in the supplied string value. For example, Instr (1,” Bangalore,” a”) returns the position of the letter “a” in the string “Bangalore” from the first (1) character.

In this case, the result is 2 because the first character position of the letter “a” is 2nd.

If we change the starting position from 1 to 3, it will return 5.

Instr (3,” Bangalore,” a”).

It returns 5 because we mentioned the starting position to look for the letter “a” only from the 3rd letter. So the position of the second appeared “a” is 5.

So, to find the space character of each string, we can use this. Once we find the space character position, we need to minus that from the total length of the string by using LEN.

For example, in the string “New York,” the total number of characters is 8, including space, and the position of the space character is 4th. So 8-4 = 4 right will extract 4 characters from the right.

Now, look at the below code for your reference.

Code:

Sub Right_Example3()

    Dim k As String
    Dim L As String
    Dim S As String

    L = Len("Michael Clarke")
    S = InStr(1, "Michael Clarke", " ")

    k = Right("Michael Clarke", L - S)

    MsgBox k
End Sub
VBA Right Example 3

In the above code variable, “L” will return 14, and the variable “S” will return 8.

In the VBA right formula, we have applied L – S, i.e., 14-8 = 6. So, from the right side, 6 characters, i.e., “Clarke.”

VBA Right Example 3-1

Loops with Right Function in Excel VBA

When we apply the VBA RIGHT function with many cells, we need to include this inside the loops. For example, look at the below image.

VBA Right Example 4

We cannot apply many lines of code to extract a string from the right side. So, we need to include loops. The below code will do it for the above data.

Code:

Sub Right_Example4()

    Dim k  As String
    Dim L As String
    Dim S As String
    Dim a As Integer

    For a = 2 To 5
        L = Len(Cells(a, 1).Value)
        S = InStr(1, Cells(a, 1).Value, " ")

       Cells(a, 2).Value = Right(Cells(a, 1), L - S)
    Next a

End Sub
VBA Right Example 4-1

The result of this code is as follows.

VBA Right Example 4-2

Recommended Articles

This article has been a guide to VBA Right Function. Here, we learn how to use the Right function in Excel VBA to extract characters from the right side of text values, along with some simple to advanced examples and a downloadable Excel template. Below are some useful Excel articles related to VBA: –