WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Right Function

VBA Right Function

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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

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.

VBA Right

Examples of Excel VBA Right Function

The following are the examples of Right Function VBA Excel.

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

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

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 up is how many characters we need from the supplied string. In this example, we need 3 characters.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

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

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 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 a result.

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

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”) returns 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 2nd 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 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, I have applied L – S, i.e., 14-8 = 6. So from right side 6 characters, i.e., “Clarke.”

VBA Right Example 3-1

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.

VBA Right Example 4

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

VBA Right Example 4-1

The result of this code is as follows.

VBA Right Example 4-2

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
4 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Right Function Excel Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More