In VBA, use Right(string, length) to return a specified number of characters from the end of a string. For example, Right("Quarterly report", 6) returns "report"; the function counts characters, including spaces, rather than words or bytes.
What the VBA Right Function Does
Right is a built-in VBA string function. It takes a text value and a number of characters, then returns that many characters starting at the right-hand end of the text. It does not change the original value: assign the result to a variable or use it directly in an expression.
This is useful whenever the part you need is defined by its position at the end of a string. Common examples include checking a file extension, reading a fixed-width code, extracting the last few digits of an identifier, or testing a text value for a known suffix. If you need characters from the beginning, use Left; if you need a segment starting somewhere in the middle, use Mid.
The function belongs to VBA, so it is available in Excel macros and other VBA-enabled Office applications. It is not the same thing as typing a worksheet formula into a cell, even though Excel has a worksheet function with the similar name RIGHT.
Syntax and Return Value
The syntax is Right(string, length). Both arguments are required:
stringis the string expression to examine. It can be a string variable, a cell value read by a macro, or a text literal.lengthis the number of characters to return from the end. It must be zero or greater.
Right returns a Variant containing a string. VBA also provides Right$, which returns a String. The dollar-sign version can be useful when you specifically want a string result, but Right is the common, readable choice in ordinary VBA code. The two forms use the same basic arguments and character-selection rule.
For example, with textValue set to "INV-8472", Right(textValue, 4) produces "8472". A length of 0 produces an empty string. If the requested length is greater than or equal to the string’s length, VBA returns the whole string rather than raising an error.
A First VBA Example
This short procedure demonstrates the call and displays its result. Before running any macro that changes a workbook, save a separate copy first: VBA macros cannot be undone with Ctrl+Z. This example only displays a message and does not alter cells.
Sub ShowRightExample()
Dim sourceText As String
Dim resultText As String
' Take the final six characters of the text.
sourceText = "Quarterly report"
resultText = Right(sourceText, 6)
MsgBox resultText ' Displays: report
End Sub
In the assignment, Right evaluates to a string and VBA stores that result in resultText. The number is counted from the end: the final character is t, and the six-character result is report. You can also pass a literal directly, as in Right("ABC-123", 3), which returns "123".
Practical Examples
A common use is extracting the extension from a filename when the input contains only a filename, not a full path. InStrRev finds the last period, and Right returns the text after it. Checking the period position first avoids treating a filename with no extension as though it had one.
Sub ShowFileExtension()
Dim fileName As String
Dim dotPosition As Long
Dim extensionText As String
' This example uses a filename, not a full path.
fileName = "budget.final.xlsx"
dotPosition = InStrRev(fileName, ".")
If dotPosition > 0 And dotPosition < Len(fileName) Then
extensionText = Right(fileName, Len(fileName) - dotPosition)
MsgBox extensionText ' Displays: xlsx
Else
MsgBox "No extension found"
End If
End Sub
The expression Len(fileName) - dotPosition calculates how many characters follow the final period. In this example the result is four, so the returned extension is xlsx. This simple example does not decide whether an extension is valid, convert its case, or handle every file-path convention; those are separate checks a larger file-processing routine may need.
Another straightforward case is checking a suffix. This procedure compares the last four characters with a target code:
Sub CheckCodeSuffix()
Dim itemCode As String
Dim suffixText As String
itemCode = "ORDER-ABCD"
suffixText = Right(itemCode, 4)
If suffixText = "ABCD" Then
MsgBox "The code has the expected suffix."
End If
End Sub
For user-entered text, consider whether case should matter. A direct string comparison is normally case-sensitive in VBA’s default binary comparison mode. If capitalization can vary, normalize both values with UCase$ or use an explicit comparison method suited to the task. Do not assume that Right itself ignores case; it only extracts text.
Spaces, Lengths, and Other Edge Cases
Spaces count as characters. Thus, if a string ends with spaces, asking for its final characters may return spaces instead of the visible letters a person expects. Use Trim first only when removing leading and trailing ordinary spaces is appropriate; trimming changes what the input means, and it does not remove every possible whitespace character, such as tabs.
Keep these boundary cases in mind:
- A length of zero returns an empty string.
- A length equal to the string’s length returns the whole string.
- A length greater than the string’s length also returns the whole string.
- A negative length causes a run-time error, commonly described as “Invalid procedure call or argument.”
- If the string expression is
Null, the result isNull; code that handles database values should account for that possibility.
When the length comes from a variable or a worksheet value, validate it before calling Right. For example, a blank cell converted or assigned unexpectedly could produce a value different from the one your code anticipated. In particular, do not subtract positions until you have checked that the relevant delimiter exists and that the resulting length is nonnegative.
Right Compared with Excel’s RIGHT
Excel’s worksheet RIGHT function and VBA’s Right function perform the same basic task: return characters from the end of text. Their context and syntax differ. In a worksheet cell, you enter a formula such as =RIGHT(A1,4). In a VBA procedure, you call Right as part of a statement or expression, such as resultText = Right(sourceText, 4).
Use the worksheet function when the result should appear in a cell and update as the source cell changes. Use VBA when the extraction is part of a macro, conditional logic, a batch operation, or another automated process. A worksheet formula is not VBA code, and VBA statements should not be pasted into a worksheet cell. Excel for Microsoft 365 on Windows supports both approaches; the VBA function’s basic syntax is the same in Excel for Mac.
Choosing Between Right, Left, and Mid
Choose the function based on where the wanted section sits, not on the kind of text being handled:
- Use
Left(text, length)for a fixed number of characters from the beginning. - Use
Right(text, length)for a fixed number of characters from the end. - Use
Mid(text, start, length)when the section begins at a specified position within the string.
For example, if a code always has a two-letter prefix and a four-character ending, Right is a natural fit for that ending. If the part to extract is between separators whose positions can vary, a fixed number of characters may not be reliable. Find the separator positions with functions such as InStr or InStrRev, then use Mid or calculate an appropriate length. This avoids assuming every input has identical formatting.
Common Problems and Debugging Tips
A frequent mistake is reversing the meaning of the length argument. Right(textValue, 3) means “return three characters from the right,” not “start three characters from the right and return everything after that.” For a variable-length suffix or text after a delimiter, locate the delimiter and calculate the required number of characters instead.
Another source of confusion is counting positions by eye when spaces or punctuation are present. Inspect the source in the VBA editor’s Locals window or display it with visible delimiters around it—for example, concatenate brackets around the value in a message box—to reveal trailing spaces. Also verify the actual length with Len before relying on a fixed-width assumption.
When extracting a suffix for comparison, decide how to handle differences in capitalization, missing characters, and unexpected punctuation. A safe routine checks the input length and format before relying on the result. When the input comes from cells, consider error values and empty cells as well; a cell error is not ordinary text and may need to be handled before passing its value to a string function.
Frequently Asked Questions
Does VBA Right count from zero or one?
It takes a character count, not a starting position. A length of one returns the final character; a length of three returns the final three characters.
What does Right return when the requested length is too large?
If the requested length is equal to or greater than the string length, it returns the whole string. A negative length is different: it causes a run-time error.
Does Right remove spaces?
No. Spaces are characters and can be included in the returned result. Use Trim separately if removing leading and trailing spaces is correct for your data.
What is the difference between Right and Right$?Right returns a Variant containing a string, while Right$ returns a String. For typical VBA text extraction, either form can be used with the same basic syntax.
Can I use Right in an Excel cell?
Use the worksheet function RIGHT in a cell, for example =RIGHT(A1,4). The VBA function is called from code, not entered as a worksheet formula.
Does Right extract the last four digits of a number?
It extracts characters from text. Convert or format a numeric value deliberately before using it, especially if leading zeros matter; numbers do not preserve those zeros unless they are represented as text or handled through an appropriate format.
Related
- How to Sum Just Subtotals in Excel Without Double-Counting
- How to Use SUBTOTAL in Excel
- X-Arcade Programming Software: What You Need to Know
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Range Properties: Values, Formatting, and NavigationIn Excel VBA, a Range property either returns information about cells or another range object, or changes a…
- VBA Loop Statements: Types, Examples, and When to Use ThemA VBA loop statement repeats a block of code, either for a set number of passes or while…
- VBA MsgBox Context: What It Means and How to Use ItIn VBA, the context argument in MsgBox is a numeric help-topic identifier: it tells the associated help system…
- VBA Macro to Create a Pivot Table in ExcelTo create a PivotTable with VBA, select a worksheet containing a header row and data, then run a…
- VBA Convert String to Uppercase with UCase ExamplesUse VBA’s UCase function to convert a string to uppercase; for a result explicitly typed as a String,…
- Input Box in Excel VBA: Use InputBox and Get DataIn Excel VBA, use InputBox to ask for a text value, or Application.InputBox when you want to restrict…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.