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

If you don’t want to use a regular expression (regex function), to extract the last n values from the end of a string, you can follow the below steps (method).

1. Count to the nth occurrence of the delimiter present in your string from the end. Mark to split by replacing it with another delimiter.
2. Split the string by this replaced delimiter.
3. Extract the second column (second value) of the split string.

I’ve already detailed the above point # 1 and # 3 in my earlier two tutorials. Here are them.

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

Regarding point # 2, it’s just about using the Split function.

First, let’s see how to extract the last n values from a string as above (without using a regular expression) in Google Sheets. Then you will get the more simple regex formula.

Extract Last N Values without Using Regular Expression in Google Sheets

In cell B2, insert the below comma separated names. Do not include the double quotes.

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

I’m going to write a formula to extract the last 3 names from this comma delimiter separated list. They are the names “Ray, Norma, Darryl“.

The below formula as per point # 1 above will replace the 3rd , delimiter from the end of the string with a > sign.

=substitute(B2,",",">",LEN(B2)- LEN(SUBSTITUTE(B2,",",""))-2)

Using the Split function (point # 2) we can split the above output into two columns.

=split(substitute(B2,",",">",LEN(B2)- LEN(SUBSTITUTE(B2,",",""))-2),">")

Finally extract the second column as per point # 3. That will be the last 3 values.

=index(split(substitute(B2,",",">",LEN(B2)- LEN(SUBSTITUTE(B2,",",""))-2),">"),0,2)

Result:

“Ray, Norma, Darryl”

Additionally, you can wrap the above formula with Trim to remove extra white space in the result. I didn’t use that function though.

We have extracted the last n (here 3) values without using a regular expression in Google Sheets.

Instead of value in cell B2, what about multiple commas or any other delimiter separated lists in a range?

See the values in B2:B4. We can use the same above formula to extract the last n values from the strings in the range. Only change the reference from B2 to B4.

=index(trim(split(substitute(B2:B4,",",">",LEN(B2:B4)- LEN(SUBSTITUTE(B2:B4,",",""))-2),">")),0,2)

This time I’ve included Trim with this array formula. It’s not necessary though.

You may have noticed that the number of values separated by the comma delimiter is different in one of the rows.

There are eight names each in cell B2 and B3. But in cell B4 there are nine names. Still, my formula is able to extract the last n values correctly from each row.

Now to the simple regex variant of the above formula. Here also I’ll provide you array and non-array formulas.

Extract Last N Values Using Regular Expression in Google Sheets

When you come to regular expressions, there are 4 supporting functions in Google Sheets. They are Query, Regexmatch, Regexextract, and Regexreplace. We will use the Regexextract function here.

I know some of you may be surprised to see the Query in the list of functions. I’ve included Query considering the Matches string comparison operator which takes regular expressions in it.

How to use Regexextract to extract the last n values from a delimiter separated string in Google Sheets?

See the below example.

In the earlier examples, the values that we have used to test were in B2:B4. This time the same values are in B8:B10.

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

The above Regexextract formula would extract the last 3 words from the end of the comma-separated value in cell B8.

Regarding the capturing group ((?:[^\,]+\,*){3})$ ;

?: – non-capturing group, i.e. (?:[^\,]+\,*){3}.
[^\,] – match a single character not present in the set. + (quantifier).
\, – match the character ,. * (quantifier).
{3} – matches 3 times.
$ – at the end of the string.

For B8:B10, we can use it as an array formula using the ArrayFormula function with Regexextract as below.

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

This time also I’ve included Trim to remove the white space at the beginning of the extracted values.

Additional Tip

Can you explain how to remove n values counting from the end of a delimiter separated list?

Yes! For example, instead of extracting the names “Ray, Norma, Darryl” from cell B8, we can remove these names and extract the other names.

I mean we can extract the names “Shawn, Ruby, Grant, Jorge, Ismael”. For this, we can use Regexreplace instead of Regexextract as below.

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

That’s all. Enjoy!

Resources:

• Extract Username from Email Address Using Regex in Google Sheets.
• Extract All Numbers from Text and SUM It in Google Sheets.
• Formula to Extract Listed Keywords from Titles in Google Sheets.
• Extract Vowels and Consonants Separately in Google Sheets.
• Extract Unique Values from a Comma Separated List in Google Sheets.
• How to Extract Multiple Words Using Regexextract in Google Sheets.
• Extract Numbers Within Square Brackets in Each Row in Google Sheets.
• Extract Numbers Prefixed by Currency Signs from a String in Google Sheets.
• How to Match | Extract Nth Word in a Line in Google Sheets.
• Regex to Replace the Last Occurrence of a Character in Google Sheets.