Excel VBA: Copy the End Part of a String with Right

To copy the last characters of a string in Excel VBA, use the Right$ function: assign Right$(text, numberOfCharacters) to another variable or cell. For example, Right$("INV-2026-0047", 4) returns 0047. If you need everything after a separator rather than a fixed number of characters, combine InStrRev and Mid$.

The Quick Answer: Use Right$

Right$ takes two arguments: the text to examine and the number of characters to return from its right-hand end. It returns a string, which you can store, display, or write to a worksheet cell. The dollar sign in Right$ indicates a string-returning version of the function; Right without the dollar sign is also valid and returns a Variant.

Here is a complete small procedure. Before running any macro, save a copy of the workbook: VBA changes cannot be reversed with Ctrl+Z.

Sub CopyLastFourCharacters()
    Dim sourceText As String
    Dim ending As String

    ' Set the example text and take its final four characters.
    sourceText = "INV-2026-0047"
    ending = Right$(sourceText, 4)

    Debug.Print ending  ' Prints 0047 in the Immediate window.
End Sub

In the Visual Basic Editor, open the Immediate window with Ctrl+G on Windows to see the result. On Mac, the editor and shortcut availability can depend on the keyboard and Excel version; the same VBA statement works in desktop Excel. Excel for the web does not run VBA macros.

How Right$ Counts Characters

The second argument is a count, not a position. If the source is "AB-73915" and the count is 5, the result is "73915". If the count is 2, the result is "15". This makes Right$ useful for fixed-width codes, trailing digits, file extensions, and other values whose ending has a known length.

The function does not remove the original text or change the source variable. It creates a returned value that your code can assign somewhere. For example, assigning the result to ending leaves sourceText unchanged. To replace a worksheet value, you must explicitly assign the returned text to a cell.

A count equal to the string length returns the entire string.
A count greater than the string length also returns the entire string; it does not add padding.
A count of zero returns an empty string.
A negative count is invalid and causes a run-time error, so check user-supplied counts before calling the function.

Put the Result in a Worksheet Cell

For a simple cell-to-cell operation, read the source cell, call Right$, and write the result to a destination cell. This example reads A2 and writes the final four characters to B2. It uses Value2, which transfers the cell value without the Currency and Date conversions associated with the older Value property.

Save a copy of the workbook before running this macro. It writes to B2, and that change cannot be undone with Ctrl+Z.

Sub CopyEndingToCell()
    Dim sourceText As String
    Dim ending As String

    ' Read A2, extract four characters, and write the result to B2.
    sourceText = CStr(ActiveSheet.Range("A2").Value2)
    ending = Right$(sourceText, 4)
    ActiveSheet.Range("B2").Value = ending
End Sub

For example, if A2 contains "SKU-8421", B2 receives "8421". The use of CStr converts ordinary numeric or text values into a string before extraction. However, it does not make every cell safe to convert: a cell containing an Excel error value needs to be checked with IsError first. Also remember that a number stored in a cell may not retain leading zeroes. If those zeroes matter, store the identifier as text rather than relying on its numeric display format.

Choose a Fixed or Variable Character Count

When the number of trailing characters changes, put the count in a variable instead of hard-coding it into the call. For instance, a macro can obtain the count from a worksheet cell or calculate it from another value. Declare the count as a Long, and validate it when it may be zero or negative.

Suppose A2 contains "ORDER-AB9012" and B2 contains 6. The value of B2 can serve as the requested count, so the extracted ending is "AB9012". This is handy when a user selects a suffix length without editing the VBA procedure. If the count comes from a cell, check that the cell contains a valid whole number before assigning it to a Long; a blank, text label, or fractional value may require explicit handling.

A fixed count is appropriate when the format is genuinely consistent, such as a four-digit code at the end of every entry. If the string format varies, a delimiter-based method is usually clearer and less likely to return the wrong characters.

Get Everything After the Last Delimiter

“Copy the end part” can mean the text after the final hyphen, slash, underscore, or other separator. In that case, counting characters is fragile: the part before the separator might have different lengths. InStrRev searches from the right and returns the position of the last matching delimiter. Mid$ then returns the text beginning just after that position.

For example, with "archive-2026-final.csv", the code below finds the last hyphen and returns "final.csv". Save a copy before running the macro if you adapt it to write results into a workbook.

Sub CopyTextAfterLastHyphen()
    Dim sourceText As String
    Dim suffix As String
    Dim separatorPosition As Long

    ' Find the final hyphen, then take the text after it.
    sourceText = "archive-2026-final.csv"
    separatorPosition = InStrRev(sourceText, "-")

    If separatorPosition > 0 Then
        suffix = Mid$(sourceText, separatorPosition + 1)
    Else
        suffix = sourceText
    End If

    Debug.Print suffix  ' Prints final.csv.
End Sub

The fallback in the Else branch is a choice, not a requirement. If no hyphen exists, you could instead return an empty string, keep the original text, or report that the expected format is missing. Decide what makes sense for the data before applying the procedure to a whole column. If the delimiter appears at the very end, the returned suffix is empty.

Handle Short, Blank, and Unexpected Values

Short strings are usually not a problem: asking for four characters from "XY" returns "XY". Blank text returns blank text. These behaviors can make a basic extraction concise, but they may not meet a data-cleaning requirement. If every result must contain exactly four characters, test the source length and flag entries that are too short rather than silently accepting them.

Be especially careful with worksheet errors such as #N/A. A cell error is not ordinary text, and converting it directly with CStr can raise an error. A production macro can check IsError(cell.Value) before conversion, then decide whether to skip the row, write a warning, or stop. When reading data from users or imported files, also consider whether leading or trailing spaces should be preserved. Right$ returns the literal ending, including spaces if they occur there; it does not trim or clean the source automatically.

Another common misconception is that a displayed suffix is always the same as the stored value. A cell formatted to display leading zeroes might contain the number 47 while showing 0047. Converting the underlying numeric value to text will not necessarily reproduce the display formatting. For identifiers and codes, storing values as text from the start is generally more reliable.

Apply the Extraction to Multiple Rows

For a list, loop through the relevant rows and write each result beside its source. This example reads A2:A10, copies the final four characters, and places the results in column B. Adjust the final row to match the data. The sample checks for cell errors before converting values, and leaves the corresponding output blank for an error or an empty source.

Save a separate copy of the workbook before running this macro. It writes to B2:B10, and macro edits cannot be undone with Ctrl+Z.

Sub CopyFourCharactersForRows()
    Dim rowNumber As Long
    Dim sourceValue As Variant
    Dim sourceText As String

    ' Process the source cells and write each suffix beside it.
    For rowNumber = 2 To 10
        sourceValue = ActiveSheet.Cells(rowNumber, 1).Value2

        If IsError(sourceValue) Or IsEmpty(sourceValue) Then
            ActiveSheet.Cells(rowNumber, 2).Value = ""
        Else
            sourceText = CStr(sourceValue)
            ActiveSheet.Cells(rowNumber, 2).Value = Right$(sourceText, 4)
        End If
    Next rowNumber
End Sub

This procedure treats an empty cell as having no output, but a cell containing a formula that returns an empty string is not necessarily the same as an actually empty cell. If that distinction matters, test the value explicitly for a zero-length string as well. For large datasets, reading a range into an array and writing results back in one operation can be faster than accessing cells repeatedly, but a row loop is easier to understand for a short list.

Right$ Compared with Mid$ and Worksheet Functions

Use Right$ when the desired piece is a known number of characters at the end. Use Mid$ when you know a starting position, or when another function such as InStrRev helps locate the start of the desired part. Left$ returns characters from the beginning. These functions solve different positional problems; choosing by the data’s structure is more reliable than forcing every extraction into a fixed character count.

For a one-time worksheet calculation, a worksheet formula may be more convenient than a macro. In Microsoft 365, the worksheet function RIGHT can return the ending characters in a cell, while VBA’s Right$ is called inside code. The worksheet function and the VBA function are related, but they are used in different places. A macro is useful when extraction is part of a repeatable workflow, writes to many cells, or needs conditional logic. VBA requires desktop Excel; macros cannot be run in Excel for the web.

Run and Save VBA Safely

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and place the procedure there. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer. On Mac, Excel also supports VBA in the desktop app, although the keyboard shortcut can vary by keyboard; use the Visual Basic command on the Developer tab if needed. The web app does not support running VBA.

Save a workbook containing macros as an Excel Macro-Enabled Workbook (.xlsm). Only run macros from sources you trust, and do not lower macro security globally just to run an unfamiliar file. Because macro actions are not undone with Ctrl+Z, make a backup before testing code that writes to cells, deletes data, or changes workbook structure. Start with a small sample range and confirm the output before expanding the procedure.

Frequently Asked Questions

How do I copy the last four characters in Excel VBA?
Assign Right$(text, 4) to a string variable or worksheet cell. For example, the last four characters of "INV-2026-0047" are "0047".

What happens if the string is shorter than the count?
Right$ returns the whole string when the requested count exceeds its length. It does not pad the result to reach the requested size.

How do I get the part after the last hyphen?
Find the last hyphen with InStrRev, then use Mid$ starting one character after its position. Check whether the hyphen was found so your code has a deliberate fallback for strings without one.

Can I copy the result directly to another cell?
Yes. Assign the result to a destination such as Range("B2").Value. Read the source value, handle error cells if necessary, and write only to the intended destination.

Can I run this VBA in Excel for the web?
No. Excel for the web does not run VBA macros. Use desktop Excel for Windows or Mac to open the Visual Basic Editor and run the procedure.

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.