How to Extract Last N Values from a Delimiter

This guide will explain how to extract the last n values from a delimiter in Google Sheets.

When working with data in Google Sheets, you may encounter delimiter-separated values. These are text values that have multiple data points separated by a delimiter, such as a space or a comma.

For example, you may have an address field in your dataset with values such as “5th Avenue, New York City, New York, United States”. The street name, city name, state, and country are all included in a single line of text but are separated by a comma.

If you need to extract the last n values from a delimiter-separated string, we can use a custom Google Sheets formula that utilizes the SPLIT function. For example, you may only need the state and country (New York, United States), which corresponds to the last two values from our address string.

In this guide, we will provide a step-by-step tutorial on how to extract the last n values from a string.

The Anatomy of the SPLIT Function

The syntax of the SPLIT function is as follows:

SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

Let’s look at each argument to understand how to use the SPLIT function.

• SPLIT() refers to our SPLIT function. This function accepts text and a corresponding delimiter string and returns an array of cells containing the split text.
• text refers to the text value you wish to divide.
• The delimiter parameter should be the character or characters to use when splitting the text.
• split_by_each is an optional argument that sets whether or not to divide the given text around each character contained in the delimiter. If set to TRUE, the text will be divided by all characters present in the delimiter argument. If set to FALSE, the text will be divided around the entire delimiter string. This argument is set to TRUE by default.
• remove_empty_text determines whether or not SPLIT will remove empty text strings from the split results. By default, this is set to TRUE.

A Real Example of Extracting Last N Values from a Delimiter

Let’s explore a few simple examples where we’ll need to retrieve the last n values from a delimiter-separated string.

Extracting Last N Values from a Sequence of Numbers

In the spreadsheet shown above, we have a string in cell B1 that we want to extract values from. The value in cell B2 also indicates we want to extract the last 3 values from the given text.

We can use the following formula to return the last 3 values from the delimited string “4,8,15,16,23,42”:

=INDEX(split(substitute(B1,",",">",COLUMNS(SPLIT(B1,","))-B2),">"),2)

Evaluating the formula, we return the string “16,23,42” which are the last three values from our comma-delimited string.

The formula works by finding the delimiter before the nth to the last value and replacing it with the “>” character. Afterward, we’ll use the SPLIT function to split the string around the “>” character. Then we’ll use the INDEX function to return the partition of the string found after the “>” character.

Extracting Last N Values from an Address

We can use the same formula to return the last two sections of a given address.

In the example above, we reused the same formula to return the state and country from an address. We’ll just need to set the value of n to 2 to disregard all but the last two components of our address.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to extract the last n values from a delimiter-separated string.

How to Extract Last N Values from a Delimiter in Google Sheets

1. Select the cell where you want to return the last n values of the target string.In this example, we want to get the last four values in the sequence of numbers in the string “4,8,15,16,23,42”.
   
2. We’ll use a custom formula to find the nth to the last delimiter and replace it with another character. We’ll then use this new character to split the original text and retrieve the latter half.
   In this example, we used the formula =INDEX(split(substitute(B1,”,”,”>”,COLUMNS(SPLIT(B1,”,”))-B2),”>”),2).
   
   
3. Hit the Enter key to evaluate the custom formula.
   In our example, the formula returns the sequence “15,16,23,42”. This string corresponds to the last four values from our comma-delimited string.
   
4. To expand this string into an array of numbers, we can use the SPLIT function.
   
   We’ll use the formula  =SPLIT(B4,”,”) to return an array consisting of the last n values from our delimited string.

These are all the steps you need to know to extract the last n values from a delimiter.

You can read our post on using the SPLIT function to learn more about how to use the function. You may also be interested in our guide on the TEXTSPLIT function to learn about a similar function to use for Excel workflows.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!