How to Use VBA Right Function in Excel (6 Examples)

The VBA Right function in Excel returns a substring from within a string based on a specified location from the right to the left of that string. We can also use several functions like the Len, InStr, or InStrRev functions to trim or extract some part of a phrase or a sentence conveniently.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to the VBA Right Function

Objective: To extract a specified number of characters from a given string from right to left.

Syntax: Right(string, length)

Arguments: The Right function has two arguments:

Argument Required/Optional Explanation
string Required It is a string expression from which the rightmost substring is extracted.
If the string contains null, it returns null.
length Required It’s a Numeric Expression that shows how many characters to return.
If 0, an empty string is returned.
If greater or equal to the number of characters, then the whole string is returned.

6 Suitable Examples of Using the VBA Right Function in Excel

1.  Extract the Last n Characters of a String Using the VBA Right Function in Excel 

The Right function can be used to extract the last n characters of a string. In the following illustration, the string “Exceldemy” contains 9 characters. We changed the length argument of the Right function while keeping the string the same to see the different outputs.

the VBA Function in Excel

Put the following code in the Visual Code Editor:

Sub RightFunction()
Range("C5") = Right("Exceldemy", 1)
Range("C6") = Right("Exceldemy", 2)
Range("C7") = Right("Exceldemy", 3)
Range("C8") = Right("Exceldemy", 5)
Range("C9") = Right("Exceldemy", 8)
Range("C10") = Right("Exceldemy", 12)
Range("C11") = Right("Exceldemy", 20)
End Sub

Read More: How to Use the VBA Chr Function (2 Examples)


2. Get the Last n Characters of a Variable with the VBA Right Function

We can store the string in a variable and apply the Right function to get the last n characters. In this example, we put the string “Exceldemy” in a variable named “variable”.

the VBA Function in Excel

The VBA code for this is:

Sub RightFunction()
Dim Variable As String
Variable = "Exceldemy"
Range("C5") = Right(Variable, 1)
Range("C6") = Right(Variable, 2)
Range("C7") = Right(Variable, 3)
Range("C8") = Right(Variable, 5)
Range("C9") = Right(Variable, 8)
Range("C10") = Right(Variable, 12)
Range("C11") = Right(Variable, 20)
End Sub

Read More: How to Use the Left Function in VBA in Excel (2 Examples)


3. Separate the Text After a Character From a String Using the Right Function

To separate a text which is after a specific character we use the Len and InStr functions along with the Right function in our VBA code.

The Len function returns the number of characters in a string. The function takes a string or a valid variable name as its argument.

 On the other hand, the Instr function checks if a substring is found in a string. It returns the position number of the first occurrence of one string within another. The result is 0 in case the string doesn’t contain the substring.

 In the following, we showed several examples. In cell C7, the output is null. There is a space after the string “John “but nothing afterward.  Cells C10, C11, and C12 contain three area codes that have a hyphen between the name of the area and the postal code. We separated the postal codes from the area names using the Right function.

the VBA Function in Excel

The VBA code is here below:

Sub RightFunction()
Range("C5") = Right("John Doe", Len("John Doe") - InStr("John Doe", " "))
Range("C6") = Right("James Bond 007", Len("James Bond 007") - InStr("James Bond 007", " "))
Range("C7") = Right("John ", Len("John ") - InStr("John ", " "))
Range("C10") = Right(Range("B10"), Len(Range("B10")) - InStr(Range("B10"), "-"))
Range("C11") = Right(Range("B11"), Len(Range("B11")) - InStr(Range("B11"), "-"))
Range("C12") = Right(Range("B12"), Len(Range("B12")) - InStr(Range("B12"), "-"))
Range("C13") = Right(Range("B13"), Len(Range("B13")) - InStr(Range("B13"), "-"))
End Sub

Read More: How to Use VBA StrConv Function (5 Examples)


Similar Readings


4. Use of the VBA Right Function to Extract the Last Word of a Sentence

We can get the last word of a sentence or phrase by using the Right, Len, and InStrRev functions in our VBA code.

As we described in the last method, the Len function returns the number of characters in a string and takes a string or variable as input.

The InStrRev function returns the position of a string inside another string like the InStr function. But it counts the position from the right end to the left. The function takes a string or a valid variable name as its argument.

In this example, we took two sentences to find out their last words using the Right function. First, we declared two variables to store the sentences and then used them as arguments of the functions.

The VBA code for this is right below:

Sub RightFunction()
Dim B5, B6 As String
B5 = "Use of the Right function"
B6 = "Extract the last word of the sentence"
Range("C5") = Right(B5, Len(B5) - InStrRev(B5, " "))
Range("C6") = Right(B6, Len(B6) - InStrRev(B6, " "))
End Sub

Read More: How to Use Fix Function in Excel VBA (4 Examples)


5. Use of the VBA Right Function to Get the Last n Characters of a Cell

To get the last n characters of a string we can use the value of the cell stored in a variable and then use it as the argument in the Right function. In the following example, we took the value of cell B5 using the Value attribute and stored it in a variable called CellValue. This variable is then used in the Right function.

Here is the code:

Sub RightFunction()
Dim CellValue As String
CellValue = ThisWorkbook.Worksheets("Sheet1").Range("B5").Value
Range("C5") = Right(CellValue, 1)
Range("C6") = Right(CellValue, 2)
Range("C7") = Right(CellValue, 3)
Range("C8") = Right(CellValue, 5)
End Sub

Read More: VBA Format Function in Excel (8 Uses with Examples)


6. Trim Off the Beginning Letters of a String Using the VBA Right Function

To remove a letter or a number of letters from the beginning of a string, we need to use the Len function along with the Right function. Let’s see the illustration where we take Castroville-95012, an area code of California. Then trim off 0, 1, 2, and 3 characters from the start of the string.

the VBA Function in Excel

Put the code in the Visual Code Editor:

Sub RightFunction()
Range("C5") = Right(Range("B5"), Len(Range("B5")))
Range("C6") = Right(Range("B6"), Len(Range("B6")) - 1)
Range("C7") = Right(Range("B7"), Len(Range("B7")) - 2)
Range("C8") = Right(Range("B8"), Len(Range("B8")) - 4)
End Sub

Read More: How to Use VBA RTrim Function (5 Suitable Examples)


Things to Remember

  • Sometimes different versions of the Right function are used to get improved performance. For example, we use Right$ as the default Right function returns a variant instead of a string.
  • If a string contains byte data, we should use the RightB function. In this case, length specifies the number of bytes.
  • The Right$ and RightB$ functions are identical to the Right function but they return a string instead of a variant data type, unlike the Right function.

Conclusion

Now, we know how to use the Right function in Excel VBA. Hopefully, it would encourage you to use the function more confidently. Any questions or suggestions don’t forget to put them in the comment box below.


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo