VBA Right Function

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.

Table of contents

• Right Function in VBA Excel
  • Examples of Excel VBA Right Function
    • Example #1
    • Example #2
  • Dynamic Right Function in Excel VBA
  • Loops with Right Function in Excel VBA
  • Recommended Articles

Now, look at the syntax below of the 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.

Learning VBA allows users to automate tasks and create custom functions in applications like Microsoft Excel and Word. Those looking to further enhance their data management skills and advance their careers can explore this Microsoft Office VBA Advanced Course.

Examples of Excel VBA Right Function

The following are examples of Right function VBA Excel.

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

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

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

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

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

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

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

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

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

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

This topic opens up many avenues for further exploration. If you’re intrigued by these concepts, this basic VBA course might provide additional insights and hands-on experience.

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

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."

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.

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

The result of this code is as follows.

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: -

• VBA WorkBook
• VBA Split
• VBA End
• Call Sub in VBA
• VBA Double