With or without using RE2 regular expressions, you can extract the last N values from a delimiter-separated string (comma, pipe, tilde, asterisk, etc.) in Google Sheets.

If you don’t want to use a regular expression, here’s a step-by-step method to extract the last N values from the end of a string without using the REGEXEXTRACT function.

How This Method Works Without Regex

1. Count the Nth occurrence of the delimiter from the end of the string and replace it with another delimiter to split on.
2. Split the string using the new delimiter.
3. Extract the second part of the split result, which gives you the last N values.

I’ve already explained points #1 and #3 in earlier tutorials:

• Substitute Nth Match of a Delimiter from the End of a String
• Select Only the Required Column from an Array Result in Google Sheets

Point #2 is straightforward—it just uses the SPLIT function.

Let’s walk through an example of how to extract the last N values from a delimiter separated string in Google Sheets—without using regex first, and then with a simple regex-based approach.

Non-Regex Formula to Extract Last N Values

In cell B2, enter this comma-separated list:

Shawn, Ruby, Grant, Jorge, Ismael, Ray, Norma, Darryl

We want to extract the last 3 names from this list, i.e., Ray, Norma, Darryl.

Here’s the formula to do that:

=ARRAYFORMULA(
  LET(
    range, B2,
    n, 3,
    on, LEN(range) - LEN(SUBSTITUTE(range, ",", "")) - (n - 1),
    IF(on < 1, range,
      INDEX(
        TRIM(SPLIT(SUBSTITUTE(range, ",", ">", on), ">")),
        0, 2
      )
    )
  )
)

We’ve now successfully extracted the last N values without using a regular expression in Google Sheets.

Where:

• B2 is the cell containing the comma delimited string
• 3 is the number of values (substrings) to extract from the end of the string
• (n - 1) makes on (occurrence number) refer to the Nth comma from the end.

If you want to apply this to a multi-row range, for example, B2:B, replace B2 with B2:B.

Formula Breakdown

Let’s break it down:

• LET(range, B2, n, 3, ...)
  Makes the formula cleaner by assigning range and the number of values n.
• LEN(range) - LEN(SUBSTITUTE(range, ",", ""))
  Counts how many commas are in the string.
• (n - 1)
  Subtracts one from n to identify the correct comma to replace from the end. For the last 3 values, we want the 3rd comma from the end, so n = 3 gives on = total commas - 2.
• SUBSTITUTE(range, ",", ">", on)
  Replaces the Nth comma (from the end) with a new delimiter (>), which will act as the split point.
• SPLIT(..., ">")
  Splits the string into two parts at the custom delimiter.
• INDEX(..., 0, 2)
  Extracts the second part—i.e., the last N values.
• TRIM(...)
  Removes any leading/trailing whitespace.
• IF(on < 1, range, ...)
  If the string has fewer values than n, just return the original string.

Extract Last N Values Using Regular Expression in Google Sheets

Now let’s simplify things using regular expressions.

Google Sheets supports regex in four functions:

• QUERY
• REGEXMATCH
• REGEXEXTRACT
• REGEXREPLACE

In this case, we’ll use REGEXEXTRACT to extract the last N values from a delimiter separated string in Google Sheets.

To extract the last 3 comma-separated values from cell B2, use:

=TRIM(REGEXEXTRACT(B2, "((?:[^\,]+\,*){3})$"))

Regex Explanation:

• (?:...) – Non-capturing group
• [^\,]+ – Matches one or more characters that are not commas
• \,* – Matches zero or more commas
• {3} – Repeats the previous pattern 3 times
• $ – Anchors the match at the end of the string

To apply this to a range like B2:B, use:

=ArrayFormula(IFNA(TRIM(REGEXEXTRACT(B2:B, "((?:[^\,]+\,*){3})$"))))

Here, TRIM is used again to clean up extra spaces from the extracted values.

Additional Tip: Remove Last N Values Instead

Want to do the reverse—remove the last N values instead of extracting them?

Let’s say you want to remove the last 3 names from cell B2 and return only the others (Shawn, Ruby, Grant, Jorge, Ismael). You can use REGEXREPLACE:

=REGEXREPLACE(B2, "((?:\,[^\,]*){3})$", "")

This removes the last 3 comma-separated values from the end of the string.

Resources

• How to Replace Multiple Comma-Separated Values in Google Sheets
• How to Count Comma-Separated Words in a Cell in Google Sheets
• Sum, Count & Cumulative Sum of Comma-Separated Values in Google Sheets
• Get Unique Values from a Comma-Separated List in Google Sheets
• Split Comma-Separated Values in a Multi-Column Table in Google Sheets
• How to Remove Duplicates from Comma-Delimited Strings in Google Sheets
• How to Compare Comma-Separated Values in Google Sheets
• How to Remove Extra Delimiters in Google Sheets
• Split Numbers from Text Without Delimiters in Google Sheets
• Insert a Delimiter After Every N Characters in Google Sheets
• How to Replace Every Nth Delimiter in Google Sheets