VBA Right Function

Right Function in VBA Excel

Right Function is same as in both worksheet function and VBA, the use of this function is that it gives us the substring from a given string but the search is done from right to left of the string, this is a type of string function in VBA used with application.worksheet function method.

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

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

Now take a look at the below syntax of the VBA RIGHT formula.

• String: This is our value and from this value, we are trying to extract the characters from the right side of the string.
• Length: From the supplied String how many characters 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” and if we want to extract only the word “Phone” we can supply the argument like the below.

RIGHT (“Mobile Phone”,5)

The reason why I mentioned 5 because the word “Phone” has 5 letters in it. In the next section of the article, we will see how we can use it in VBA.

Examples of Excel VBA Right Function

The following are the examples of Right Function VBA Excel.

Example #1

I 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 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 for this variable, we will assign the value by applying the RIGHT function.

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 up 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 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”.

Now I 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 F5 key then, we will get “York”.

Example #2

Now take a 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” as the result.

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.

Dynamic Right Function in Excel VBA

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

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

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

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

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

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

The reason why it returns 5 because I mentioned the starting position to look for the letter “a” only from the 3^rd letter. So the position of the second appeared “a” is 5.

So, to find the space character of the of each string we can use this. Once we find the space character position we need to minus that from a 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 4^th. 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 variable “S” will return 8.

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

Loops with Right Function in Excel VBA

When we need to 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 the 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 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