VBA Right Function
- VBA Right is categorized under text or String function, useful to chop characters from a string.
- It can be used as either procedure or function in a vba editor window.
- It is frequently used to extract a substring from a full-text string, i.e. either to pull out the last name from a full name or to extract email extension from email id.
- VBA Right function allows you to extract the right-side part of the string from a full-text string.
- VBA string functions do not change the original string. Usually, they will return a new string based on the function you input in the code.
VBA RIGHT function returns a substring from within a supplied text or a string i.e. starting with the right-most character (from the end of the string).
Syntax of Right in Excel VBA
The syntax for the VBA Right function in excel is as follows:
Right(Str,Length)
- Str: It is a text String from where you need to extract the specified number of characters.
- Length: Number of characters to extract or to return from starting from extreme rightmost character or position, it should be the numeric expression which indicates how many characters to return.
Note: For length argument, If it is mentioned 0, it will return a zero-length string (“”). If the length argument entered is equal to the number of characters of string or if it is greater than the number of characters of string then it will result in or return an entire string. If length is Null or less than 0, it will return an error message
How to Use VBA Right Function in Excel?
We will learn how to use a VBA Right function with example in excel.
Example #1 – VBA Right
Step 1: Select or click on Visual Basic in the Code group on the Developer tab or you can directly click on Alt + F11 shortcut key.
Step 2: Insert a module from the insert tab to start writing codes in VBA.
Step 3: Under the Microsoft Excel objects, right-click on sheet 1 (VB_RIGHT) Insert and under the menu section select Module so that a new blank module gets created.
Now the blank module is created, it is also called a code window, where you can start writing VBA RIGHT function or procedure codes.
Example #2 – VBA Right Function Code
Step 1: Suppose, I have the word “THURSDAY” and I want to extract the right side word i.e. “DAY” from this sentence with the help of VB RIGHT function macro code.
Step 1: In the VBA editor, I have a given a name as VBA_R() after typing Sub.
Code:
Sub VBA_R() End Sub
Step 2: As VBA RIGHT function is categorized under string type Variables, DIM (Dimension) is used in a VBA code to declare a variable name, it’s typing.
In VBA, you need to always declare initially that you are setting up a variable. This is done (mostly) with the word Dim. DIM is used to declare variable & storage allocation for the variable.
Code:
Sub VBA_R() Dim rght As String End Sub
Step 3: After declaring a variable, Assign a value to this variable with the help of the RIGHT function.
Code:
Sub VBA_R() Dim rght As String rght = right( End Sub
First parameter or argument is String i.e. It is a text String from where you need to extract the specified number of characters. Here it is “THURSDAY”
Note: When using strings argument, you have to insert the text value in quotation mark.
Step 4: Length as long: Number of characters to extract or to return from starting from extreme rightmost character or position, it should be the numeric expression which indicates how many characters to return, here I want the word “DAY” so the number of characters is 3.
Code:
Sub VBA_R() Dim rght As String rght = right("THURSDAY", 3) MsgBox rght End Sub
Here, I have completed the RIGHT function arguments here. Now, I want to display this result of the variable in the message box. on the next line of code, let’s click on Ctrl + Space, type MsgBox after this you can mention a variable name.
Step 5: Now, the code is ready, I can run it by click on the F5 key. once I do that, Pop up message appears, with a result in it as “DAY”
Example #3 – VBA Right
Now, instead of output appearing in the message box, I want the result or output data to appear in the worksheet.
In the worksheet, I have a data, i.e. USA city & state (in abbreviated form) in the cell “A4”, now I want to extract state (in abbreviated form), Here I can use Right function with slight modification in the code. Let’s check out, how it can be done.
Step 1: After declaring a variable, I have to input the variable name again and cell address of the full-text string with the help of Range or cell function.
Code:
Sub VBA_R2() Dim rght As String rght = Range("a4") End Sub
Step 2: Now, again I need to enter the variable name, and apply RIGHT function & input its arguments.
Code:
Sub VBA_R2() Dim rght As String rght = Range("a4") rght = right("Carlsbad, CA", 2) End Sub
Step 3: In the previous example, I entered the msg box for the result to be displayed, now, I want the result to appear in a specific cell in a worksheet. for this, I need to enter range or cell function for the result to be displayed.
Code:
Sub VBA_R2() Dim rght As String rght = Range("a4") rght = right("Carlsbad, CA", 2) Range( End Sub
Step 4: Now, let’s apply range function, initially I need to input the range or cell function and Later enter the variable name, so that in that specific cell (“B4”), the result appears.
Code:
Sub VBA_R2() Dim rght As String rght = Range("a4") rght = right("Carlsbad, CA", 2) Range("B4").Value = rght End Sub
Step 5: Now, the code is ready, I can run it by clicking on the F5 key. once I do that, you can observe an output value of Right function appearing in the cell “B4”
Thinks to Remember
- Most of the cases, for better performance different version of the Right function, is used i.e. Right$ instead of the default Right is used. This is because the Right function returns a variant instead of a string as therefore it impacts better performance in the result
- RIGHTB i.e. Right function with byte data: In this function, length argument specifies the number of bytes to return
- Both RIGHT$ & RIGHTB$ function is usually used to return a byte data from a String data type instead of Variant/String data type
Recommended Articles
This is a guide to VBA Right. Here we discuss how to use Excel VBA Right function along with practical examples and downloadable excel template. You can also go through our other suggested articles –
