Al Arafat Siddique
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.
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 SubRead 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 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 SubRead 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 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 SubRead More: How to Use VBA StrConv Function (5 Examples)
Similar Readings
- How to Use VBA Space Function in Excel (3 Examples)
- Excel Formula to Generate Random Number (5 examples)
- Excel Date Picker for Entire Column
- How to Remove Duplicates in Excel Sheet (7 Methods)
- How to Use Log Function in Excel VBA (5 Suitable Examples)
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 SubRead 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 SubRead 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.
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 SubRead 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.
