Excel Functions Tutorials

- VBA
- VBA Tutorial
- VBA Functions in Excel
- VBA ArrayList
- VBA Arrays Function in Excel
- VBA Activate Sheet
- VBA Break
- VBA Borders
- VBA Boolean
- VBA ByRef
- VBA Code
- VBA Const
- VBA Class Modules
- VBA Count
- VBA COUNTA
- VBA COUNTIF
- VBA Comment Block
- VBA Match Function
- VBA LEFT Function
- VBA Right Function
- VBA Like
- VBA LEN
- VBA Long
- VBA Today
- VBA Now
- VBA Time Function
- VBA Timer
- VBA TimeValue
- VBA Weekday
- VBA ROUND
- VBA RoundUp
- VBA Random Numbers
- VBA ReDIM Function
- VBA Rename Sheet
- VBA Protect Sheet
- VBA Remove Duplicates
- VBA Concatenate
- Copy Paste in VBA
- VBA Paste
- VBA Print
- VBA Date Function
- VBA DateDiff Function
- VBA DateAdd Function
- VBA DatePart
- VBA Data Type
- VBA Dictionary
- VBA Debug Print
- VBA Charts
- VBA CDBL
- VBA CSTR
- VBA Chr
- VBA ChDir
- VBA ENUM
- VBA RegEx
- VBA Mid Function
- VBA Max
- VBA Find Function
- VBA Find and Replace
- VBA Trim Function
- VBA Text
- VBA OFFSET Function
- VBA MOD Function
- VBA Split Function
- VBA UBound Function
- VBA Union
- VBA Transpose
- VBA INT
- VBA InStr
- VBA INSTRREV
- VBA Intersect
- VBA Integer
- VBA DIR Function
- VBA OR Function
- VBA AND
- VBA Operators
- VBA Not Equal
- VBA Worksheet Function
- VBA Workbook
- VBA ThisWorkbook
- VBA Worksheets
- VBA Write Text File
- VBA Hyperlinks
- VBA String Functions
- VBA StrComp
- VBA StrConv
- VBA Sub
- VBA Call Sub
- VBA End
- VBA Wait
- VBA Option Explicit
- VBA SubString
- VBA Subscript Out of Range
- VBA IIF
- VBA IF OR
- VBA IFERROR
- VBA On Error
- VBA OverFlow Error
- VBA 1004 Error
- VBA Error Handling
- VBA Type
- VBA Type Mismatch Error
- VBA IsEmpty
- VBA ISNULL
- VBA Input Box
- VBA MsgBox
- VBA Text Box
- VBA Format
- VBA Format Number
- VBA Conditional Formatting
- VBA AutoFill
- VBA AutoFilter
- VBA Color Index
- VBA Font Color
- VBA Clear Contents
- VBA Collection
- VBA Paste Special
- VBA Progress Bar
- VBA GoTo
- VBA Userform
- VBA Close UserForm
- User Defined Function in Excel VBA
- VBA Outlook
- VBA JOIN
- VBA LCase
- VBA UCase
- VBA Select Case
- VBA Select Cell
- VBA Selection
- VBA Active Cell
- VBA Set
- VBA Sleep
- VBA Pause
- VBA Range Objects
- VBA Range Cells
- VBA UsedRange
- VBA Loop
- VBA Break For Loop
- VBA For Each Loop
- VBA For Next Loop
- VBA Do Until Loop
- VBA File Copy
- VBA FileDialog
- VBA FileSystemObject (FSO)
- VBA Cells
- VBA Last Row
- VBA Insert Row
- VBA Hide Columns
- VBA New Line
- VBA GetOpenFilename
- VBA GetObject
- VBA Delete File
- VBA Delete Row
- VBA Insert Columns
- VBA Delete Column
- VBA Val
- VBA Value
- VBA Variant
- VBA Variable Declaration
- VBA Global Variables
- VBA Pivot Table
- VBA Refresh Pivot Table
- VBA PowerPoint
- VBA Send Email from Excel

- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)

Related Courses

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

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

4.6 (247 ratings)

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

- 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

- 3 Courses
- 12 Hands-on Projects
- 43+ Hours
- Full Lifetime Access
- Certificate of Completion