Right in Excel VBA: Syntax, Examples, and Tips

VBA & macros · Updated November 18, 2026

In Excel VBA, Right returns a specified number of characters from the end of a string. For example, Right$(sourceText, 4) returns the last four characters; use it when a macro needs to extract a suffix, code, or other text at the right-hand end of a value.

What Right Does in VBA

VBA’s Right function reads text from right to left and returns the requested number of characters from the end. It does not search for a particular character or interpret what the text means. If a product code is ZX-8047, asking for four characters returns 8047. Asking for three returns 047.

The function works on a string, which might come from a cell, a variable, or a value your macro has constructed. It is often used to extract file extensions, short identifiers, trailing digits, or a fixed-width suffix. The returned value is text, even when the characters happen to look like a number.

In VBA, both Right and Right$ are available. The dollar-sign form returns a String rather than a Variant, making it a clear choice when the input is text and the result should be text. For ordinary string extraction, their visible results are the same.

Syntax and Arguments

The VBA syntax is Right(string, length). Both arguments are required. string is the text to inspect, and length is the number of characters to return from its right end. The length can be a numeric variable or an expression, not just a fixed number.

  • If the requested length is zero, the result is an empty string.
  • If the requested length is greater than or equal to the string’s length, VBA returns the whole string.
  • If the requested length is negative, VBA raises an invalid procedure call or argument error.
  • If the input is an empty string, the result is empty.

For example, a string containing AB12 produces 12 when the requested length is two. The function counts characters from the end; it does not skip spaces or punctuation. If the text ends in two spaces and you request two characters, the result consists of those spaces.

A Simple Excel VBA Example

This macro reads the value in A2, takes its last four characters, and writes the result to B2. It assumes A2 contains ordinary text or a value that VBA can convert to text.

Sub ExtractLastFour()
    Dim sourceText As String
    Dim lastFour As String

    ' Read A2, then place its final four characters in B2.
    sourceText = CStr(ActiveSheet.Range("A2").Value2)
    lastFour = Right$(sourceText, 4)
    ActiveSheet.Range("B2").Value = lastFour
End Sub

To use it in Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, paste the procedure into the module, and run it with F5 while the cursor is inside the procedure. On a Mac, open the editor from Developer > Visual Basic; the keyboard shortcut can depend on the keyboard and function-key settings. If the Developer tab is hidden, enable it in Excel’s ribbon settings. The macro uses the active sheet, so select the intended worksheet before running it.

For a cell containing ZX-8047, the macro writes 8047 to B2. The CStr conversion makes the input a string before extraction. It does not preserve a cell’s display formatting: if A2 is a number displayed with leading zeros, the underlying value may not contain those zeros. Keep identifiers that require leading zeros as text, or deliberately convert the value using a suitable format before extracting characters.

Right in VBA Versus RIGHT in a Worksheet

Excel also has a worksheet function named RIGHT. It serves a similar purpose but is entered in a cell, while VBA’s Right is used in code. For a worksheet formula, with text in A2 containing ZX-8047, enter =RIGHT(A2,4) in B2; the result is 8047. The formula can fill down alongside a list without running a macro.

The worksheet function’s second argument, the number of characters, is optional. If omitted, Excel returns one character. VBA’s Right requires both arguments, so the equivalent VBA call must include a length. A worksheet formula is often simpler when the result should remain visible and update automatically as source cells change. VBA is useful when extraction is part of a larger automated process, such as preparing a report or moving results to another location.

Do not confuse the shared name with identical calling rules. In a VBA procedure, use the built-in function as Right$(textValue, 4). If you want a cell formula, enter the worksheet expression in the cell or assign that formula through VBA. The function you choose depends on where you want the logic to live: in worksheet calculations or in a macro.

Useful Text-Extraction Examples

Right is most dependable when the part you need has a known length or always appears at the end. Common uses include:

  • Product or order codes: take the final digits from a code when the format is consistent, such as the four-digit suffix in ZX-8047.
  • File extensions: inspect the last three or four characters when filenames follow a known pattern. For mixed extensions, searching for the final period is usually more robust than assuming every extension has the same length.
  • Fixed-width data: extract a trailing field when a text file or export defines a stable number of characters for that field.
  • Validation: compare a suffix against an expected ending before a macro processes a record.

For example, if filenames are in column A and all relevant extensions are exactly three letters after the period, a macro could take the final three characters and compare them with an expected extension. That shortcut is not suitable if the data includes extensions of different lengths, filenames without extensions, or trailing spaces. Decide what the input can contain before relying on a fixed character count.

Common Problems and Edge Cases

A frequent surprise is that spaces count as characters. A cell containing Report has a trailing space, so taking the last character returns a space rather than the final letter. If unwanted surrounding spaces are possible, clean the text first. VBA’s Trim$ removes leading and trailing ordinary spaces, but it does not remove every possible whitespace character, such as tabs.

Another issue is confusing displayed values with stored values. A cell may display 000123 because of its number format while storing the number 123. Converting its underlying value to text and taking the last characters cannot recover zeros that are only part of the display format. For codes, account numbers, and similar identifiers, storing the original characters as text is generally the safer data layout.

Check the requested length before calling the function if it comes from a worksheet or another variable. A negative length causes an error, while zero returns an empty string. Also account for error values in cells: converting an Excel error to a VBA string is not the same as converting ordinary text and can cause a runtime error. A robust macro checks input cells with IsError before converting them.

Finally, a suffix is not necessarily unique or meaningful by itself. Two different codes can share the same final four characters. If you are identifying records, confirm that the suffix is sufficiently distinctive and that the source format is consistent rather than assuming an extracted fragment uniquely identifies the original value.

Choosing Right or Another VBA Function

Use Right when the information you need is positioned at the end and its length is known. Use Left for characters at the beginning, and Mid when you need characters starting at a position inside the text. When the required part has a variable length, locate a meaningful delimiter instead of choosing a fixed count. For example, an extension is usually better defined as the text after the last period than as “the last three characters” if extensions vary.

For more complex cases, VBA’s InStrRev can find the last occurrence of a character, such as a period or backslash, and the position can guide further extraction. This is useful for filenames with different extension lengths or paths with several folder separators. It does require extra logic for cases where the delimiter is missing, so define how the macro should handle those records.

In short, fixed-position extraction is simple and readable, but it relies on a stable input format. If the source data changes shape, a delimiter-based approach or a worksheet formula built around the actual pattern is usually easier to maintain.

Running Macros Safely

Before running any macro that changes cells, save a separate copy of the workbook. Excel’s Ctrl+Z undo command cannot undo changes made by VBA. The small example above writes to B2, but a procedure that loops through rows or overwrites a column can affect many records at once.

Save a workbook containing VBA in a macro-enabled format such as .xlsm; a standard .xlsx file does not retain VBA code. Only enable macros in files you trust. Do not weaken Excel’s macro security settings simply to run an unfamiliar workbook. On Windows, macro security options are available through File > Options > Trust Center > Trust Center Settings > Macro Settings. Menu labels and available security controls can differ on Mac.

Frequently Asked Questions

What does Right do in Excel VBA?
It returns a specified number of characters from the end of a string. For example, requesting four characters from ZX-8047 returns 8047.

What is the syntax for Right in VBA?
Use Right(string, length) or Right$(string, length). Both arguments are required in VBA; the second argument gives the number of characters to return.

What is the difference between Right and Right$?
Both extract characters from the right. Right returns a Variant containing a string, while Right$ returns a String. For a text result in VBA, the dollar-sign form makes the intended type explicit.

Can I use Right to get a file extension?
Yes, if the extension always has a known length. If filenames have extensions of varying lengths, find the final period and extract the text after it instead of assuming a fixed number of characters.

Why does Right return a space or omit leading zeros?
Spaces are characters, so trailing spaces are included in the count. Leading zeros may be absent because Excel stores a cell as a number and only displays zeros through formatting. Trim unwanted spaces and store identifiers as text when their zeros are part of the data.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.