Extract Text After the Last Instance of a Character in Excel

When working with structured data, product codes, or imported files, you often need to pull out the portion of a string that comes after the final occurrence of a specific character like the part after the last hyphen in a product code or after the last slash in a file path. Excel doesn’t have a single built-in function for this, but you can easily achieve it using formulas or a simple VBA custom function.

In this article, we’ll cover five different techniques to extract text after the last instance of a character. These methods range from quick formulas using RIGHT, SEARCH, and SUBSTITUTE, to Excel 365’s modern TEXTAFTER, and even a VBA function for extra flexibility.

When working with structured data such as product codes, tracking numbers, or shipment tags, you often need the portion of text that appears after the final hyphen. Using RIGHT, SEARCH, and SUBSTITUTE functions together gives you precise control even when the number of hyphens varies across cells. This method ensures accurate extraction without manually counting delimiters or adjusting formulas for each case.

We’ll use the following dataset:

Steps:

➤ Place your dataset in A2:A11, ensuring each cell contains text separated by hyphens.
➤ In B2, type the following formula to locate the last hyphen and return the characters to its right:

➤ Press Enter to apply the formula to the first row.
➤ Drag the fill handle down to copy the formula for all rows in your dataset.
➤ Review the extracted results in column B, which now display only the text appearing after the final hyphen in each entry.

If you’re looking for a simpler, more compact formula that avoids using SEARCH function, this method is a great choice. By padding each hyphen with spaces using REPT function, then trimming the result, you can easily isolate the last segment of text after the final hyphen. This approach is especially useful when working with moderately sized datasets where formula readability is a priority.

Steps:

➤ Place your dataset in A2:A11, making sure each cell contains hyphen-separated text values.
➤ In B2, type this formula to replace hyphens with spaces and return the trailing segment:

➤ Press Enter to apply the formula to the first row.
➤ Drag the fill handle down to apply the formula to all rows in your dataset.
➤ Verify that column B now cleanly displays only the portion of text appearing after the last hyphen in each entry.

For users working with modern versions of Excel, TEXTAFTER function provides the quickest and cleanest way to extract text after the final hyphen. By specifying a negative instance, Excel directly targets the last occurrence of the delimiter without needing extra functions. This method is perfect for large datasets or when you want a highly readable formula that minimizes complexity.

Steps:

➤ Ensure your hyphen-separated values are listed in A2:A11, ready for extraction.
➤ In B2, type this simple formula to retrieve the segment after the final hyphen:

➤ Press Enter to apply the formula to the first cell.
➤ Drag the fill handle down to process all rows in your dataset.
➤ Check column B to confirm that only the values appearing after the last hyphen are instantly extracted for every entry.

For advanced Excel users seeking flexibility, combining REPLACE with XLOOKUP function is an effective solution. This method pinpoints the last hyphen (or other delimiters) and trims everything before it, leaving only the trailing text. Its adaptability makes it ideal for complex datasets where delimiter positions vary or when you need to switch between hyphens, underscores, or other symbols.

Steps:

➤ Prepare your hyphen-separated data in A2:A11, ensuring each string is formatted consistently.
➤ In B2, enter the following dynamic formula to extract everything after the last hyphen:

➤ Press Enter to confirm and view the first result.
➤ Copy or drag the formula downward to apply it across all rows in your dataset.
➤ Review column B to confirm that only the text following the final hyphen is displayed for each entry.

When you frequently need to extract text after the last instance of a character, creating a custom VBA function is a smart solution. This method works with any delimiter, handles large datasets efficiently, and spares you from typing complex formulas repeatedly. It’s perfect for automating recurring text-processing tasks across multiple worksheets or workbooks.

Steps:

➤ Press  Alt  +  F11  to open the VBA Editor, which allows you to create and store custom Excel functions.
➤ Click Insert, then choose Module, and paste the following code:

Function AfterLastChar(txt As String, delim As String) As String
    Dim pos As Long
    pos = InStrRev(txt, delim)
    If pos > 0 Then
        AfterLastChar = Mid(txt, pos + 1)
    Else
        AfterLastChar = txt
    End If
End Function

➤ Close the VBA editor and return to your worksheet.
➤ In B2, enter this formula to apply your new function:

➤ Press Enter to confirm, then drag or copy the formula down to process all rows.
➤ Column B now displays only the text after the final hyphen for each entry, no matter how many delimiters the string contains.

Frequently Asked Questions

Why does my extraction formula return #VALUE! even when a hyphen exists?

This error usually happens if the delimiter count is inconsistent or there are hidden spaces. Double-check your data, apply TRIM, or wrap your formula with IFERROR function to return a fallback result instead of an error.

Can I extract text after the last occurrence of other characters besides hyphens?

Yes. Replace the hyphen (“-“) in your formula with your desired delimiter, such as slashes (“/”), underscores (“_”), or dots (“.”). These methods include TEXTAFTER function and VBA that work with any specified character effectively.

Is TEXTAFTER better than RIGHT + SEARCH for modern Excel users?

Yes. TEXTAFTER function simplifies the task by directly extracting after the last occurrence using -1. Older formulas like RIGHT and SEARCH remain useful for compatibility with Excel 2019 or earlier versions without TEXTAFTER function support.

When should I use a VBA custom function instead of a formula?

Choose VBA when repeatedly processing large datasets, working with multiple delimiters, or automating tasks. VBA reduces repetitive formulas, increases flexibility, and saves time when extracting text after the last instance of characters.

How can I clean up extracted text if extra spaces or characters appear?

Use TRIM function to remove leading or trailing spaces. Combine with CLEAN function for non-printable characters. These functions ensure your extracted results remain neat, especially when handling imported, inconsistent, or unformatted source data.

Wrapping Up

In this tutorial, you learned five effective techniques to extract text after the last instance of a character in Excel starting from robust formulas using RIGHT, SEARCH, and SUBSTITUTE, to the modern TEXTAFTER function, and even a custom VBA solution. Whether you’re handling product codes, file paths, or location strings, these methods will simplify your text manipulation tasks and keep your datasets organized. Feel free to download the practice file and share your feedback.