VBA Right Function
The Excel VBA Right function extracts characters from the right side of an expression, ending with the last character in the expression. A user-specified number sets the length of the subset returned, which is a string data type and includes any blank spaces.
Syntax:
Right(Expression,N)
The VBA Right function accepts two arguments, as described below.
Arguments:
Expression | - the expression from which a substring on the right is extracted |
N | - number of characters from the right to return |
Notes about usage:
- Expression may be any valid VBA data type, except array or object.
- Right will accept an element of an array for the expression.
- If Expression is a zero-length string (""), then a zero-length string ("") is returned for all valid values of N.
- If N is a fractional number, then it is rounded to the nearest Long integer.
- If the rounded value of N is a negative number, then an error message is generated and the program execution ends.
- If the rounded value of N is 0, then a zero-length string ("") is returned.
- If N is greater than or equal to the number of characters in Expression, the entire string is returned.
- The value returned is string, regardless of the expression data type.
- Using an expression of array or object data type results in an error message and program execution stops.
- The number of characters to return is a required argument. If omitted, an error messages is generated and program execution stops. Note: This function can also be used as part of an Excel spreadsheet formula, and its behavior here is different. Here, a zero-length string is returned ("").
How to use the VBA Right function
Dim str1 As String
str1 = "Last Name, First Name, Address"
MsgBox Right(str1, 1)
Returns "s"
MsgBox Right(str1, 4)
' Returns "ress"
MsgBox Right(str1, 8)
' Returns " Address"
In the first use of right, the last letter "s" is returned. Asking for four (4) characters to be returned results in "ress." With a request for eight (8) characters to be returned results in " Address," since blank spaces count as characters.
Use the VBA Right function with extra spaces
Here, we consider two expressions conveying the same textual message, but one has extra blank spaces.
Dim str1, str2 As String
Str1 = "This example has lots of spaces"
str2 = " This example has lots of spaces "
MsgBox Right(str1, 1)
Returns "s"
MsgBox Right(str2, 1)
' Returns " "
MsgBox Right(str1, 16)
' Returns "s lots of spaces"
MsgBox Right(str2, 16)
' Returns "ots of spaces "
The first string, str1, is of length thirty-one (31) characters, while str2 is of length forty-one (41), since blanks count as characters. If we use Right to extract the last letter of these strings, the value returned with str1 is the desired "s," while we will get a space with str2. And the difference between the letters extracted is even more pronounced when asking for sixteen (16) characters to be returned.
Imagine having a list of names from which you need the last name, for example. Having extra unknown spaces can lead to incorrect results.VBA
Using the VBA Right function without a length argument
Dim str1 As String
Str1 = "This example has lots of spaces"
MsgBox Right(str1)
If the length argument is omitted from the Right function, then VBA generates a Compile error saying that the argument is not optional. Further program processing ends.
How to use the Excel VBA Right and Left functions to extract a substring within a string
Sometimes, we need to find and return a substring in the middle of an expression, rather than at one of the ends of it. This example shows how to do this by using the VBA Left and Right functions together.
str1 = "LastName, FirstName"
N1 = 9
N2 = 5
MsgBox Left(Right(str1, N1), N2)
' Returns "First"
N1 = 15
MsgBox Right(Left(str1, N1), N2)
' Returns "First"
Right(str1, 9) returns the last nine (9) characters at the end of str1, which are "FirstName." Then, within this new substring, we are asking for the first five (5) characters, which are "First."
Essentially, we are telling VBA to pull out the substring beginning in position eleven (11) and ending in position fifteen (15). Confused? Consider that Str1 is of length nineteen (19). If we ask for the last nine characters, we are saying, "begin in position eleven (11) and return everything to the right. Then from that segment, return the first five (5) characters to the left."
Equivalently, we could have gotten the same result by using Right(Left(str1, 15), 5).
These methods of extracting a substring in the middle are cumbersome, but fortunately, VBA provides another function to address this need without all the mathematical contortions. It's called the Mid function.