• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

VBA Right Function

Home » VBA » VBA String and Text Functions » VBA Right Function

By Sharmila Reddy Leave a Comment

VBA Right

Right Function in VBA Excel

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.

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.

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 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
Cyber Monday Sale
VBA Training (3 Courses, 12+ Projects) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
4.6 (247 ratings)
Course Price

View Course

Related Courses

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 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 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 the 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”) 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 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 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” a total number of characters are 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 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. 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 –

  • Global Variables in VBA
  • VBA WorkBook Examples
  • Excel VBA Split String 
  • How to Record Macros in Excel?
  • Examples of End Function in VBA Excel
  • How to Call Sub in Excel? 
  • Formula of Right in Excel
  • RIGHT in Excel
  • Use Text Box in VBA
  • VBA Lowercase
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 >>

Filed Under: VBA, VBA String and Text Functions

Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. 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

Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Download VBA Right Function Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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