VBA Right Function

Excel provides many inbuilt text functions that helps the users to deal with text-based data. Some of these functions are LEN, LEFT, RIGHT, function to extract characters from specified string data. The common instance of using these functions is extracting the first name & last name from the specified string.

If you have already worked with the RIGHT function in the Excel worksheet, you can easily work with the VBA RIGHT function because both are exactly the same. This tutorial will discover the definition, syntax, arguments, and various examples explaining the real use of the VBA RIGHT function.

What is VBA Right Function?

The VBA RIGHT Function is used to extract the length of characters (or a substring) only from the right side of the supplied string.

Using this function in a VBA code returns a substring from the input string towards the ending of the string. In other words, the VBA RIGHT function only fetches the substring from the right side of the input string. For instance, you have an input string, “Hello World”, you can fetch the first 5 characters, i.e., WORLD, using the VBA RIGHT function.

This function can be used as a VBA function and a worksheet function in excel. It can be used as a part of the formula in the cell. The VBA RIGHT function is listed under the text category of VBA functions. It just functions like the RIGHT function in the Excel worksheet.

Syntax

Parameter

• String (required): The String argument represents the length of the string we are trying to extract.
• Length (required): This length argument represents but how many characters you want to fetch from the right side of the specified String.

Return

The VBA RIGHT Function returns a substring after extracting the rightmost string from the main string.

Points to Remember

1. The VBA RIGHT function returns a string-based output for a given string.
2. IF the specified string argument is Null, this function will return Null as the output.
3. As the name suggests, the VBA RIGHT function extracts only the rightmost characters from the string.
4. When the RIGHT function is combined with the InStr function, we can find the space in VBA; by this, a user can easily differentiate the word in the specified string.

Examples

#RIGHT Function Example 1: Fetch the last name from the string “Reema Panda”.

VBA RIGHT is one of the inbuilt functions that many users use to fetch only a part of the String (from the RIGHT side of the String) or value given by the user.

Below given are the steps to write a VBA macro to fetch the first name from the specified string using the VBA RIGHT function:

Step 1: Open the VBA developer tab. Go to Excel worksheet, from the ribbon tab click on developer window -> visual basic editor or directly click on the shortcut keywords Alt +F11.

Step 2: The VB Editor window will be displayed. The next step is to create a module. From the ribbon tab click on Insert-> Click on Module.

Step 3: You will notice, VBA will insert a new module window. Start the program by introduce your macro name followed by the declaration of the variable.

Step 4: Next, we will incorporate the RIGHT function. In the arguments, we will pass the String, and in the length argument, we will pass 5 since we want to extract the first 5 characters from the rightmost part of the String.

Step 5: Store the fetch value in our variable and later return the variable using MsgBox.

Refer to the below Code:

Output

Run your macro either by clicking Run or by pressing the F5 key. As a result, you will notice VBA will throw a MsgBox displaying the first 5 characters from the rightmost side of your string.

The macro has extracted the last 5 characters from the string “Reema Panda.” So the output will display first name, i.e., “Panda”.

#RIGHT Function Example 2: Dynamically fetch the last name from employee table.

Many often, you are asked to extract the last name from the Excel table. VBA RIGHT function is a great advantage to solve these types of cases.

Below given in an employee table. Let’s see another example where we will be using the VBA RIGHT Function for extracting the last name of the employee from the employee table.

Below given are the steps to write a VBA macro to fetch the first name from the specified employee table using the VBA RIGHT function:

Step 1: Open the VBA developer tab. Go to Excel worksheet, from the ribbon tab click on developer window -> visual basic editor or directly click on the shortcut keywords Alt +F11.

Step 2: The VB Editor window will be displayed. The next step is to create a module. From the ribbon tab click on Insert-> Click on Module.

Step 3: You will notice, VBA will insert a new module window. Start the program by introduce your macro name followed by the declaration of the variable.

Refer to the below given macro code:

Step 4: Since we have to apply the function for a range of cells in Excel so we will incorporate the FOR loop. Start loop and assign variable LstNme the result of the VBA RIGHT function.

Refer to the below code:

Step 5: Just to confirm that your program has run successfully, add a MsgBox displaying a message.

Refer to the below code:

Output

Run your macro either by clicking Run or by pressing the F5 key. As a result, you will notice VBA will throw a MsgBox and will fetch the last names of each Employee from the Employee table.