The SPLIT function in Google Sheets can be used to divide text based on a specific delimiter.
To get the last item that results from the SPLIT function, you can use the following syntax:
=INDEX(SPLIT(A2, " "), COLUMNS(SPLIT(A2, " ")))
This particular example will split the text in cell A2 using a space as a delimiter and then it will return only the last item that results from the split.
For example, if the string in cell A2 is Andy Bernard Douglas then this formula will return Douglas.
The following example shows how to use this formula in practice.
Related: How to Use SPLIT Function and Get First Item in Google Sheets
Example: How to Use SPLIT Function and Get Last Item in Google Sheets
Suppose we have the following column of names in Google Sheets:
Suppose we would like to split the names based on where spaces occur and then get only the last item that results from the split.
We can type the following formula into cell B2 to do so:
=INDEX(SPLIT(A2, " "), COLUMNS(SPLIT(A2, " ")))
We can then click and drag this formula down to each remaining cell in column B:
The formula splits the names in column A based on where the spaces occur and then only returns the last item from the split.
How This Formula Works
Recall the formula that we used to split the text and get the last item from the resulting split:
=INDEX(SPLIT(A2, " "), COLUMNS(SPLIT(A2, " ")))
Here is how this formula works:
First, the SPLIT function splits the text in cell A2 into new columns based on where the spaces occur.
For example, the name Andy Bernard Douglas is split into three columns.
Next, the COLUMNS function returns the total number of columns that result from the split.
For example, in this example it returns a value of 3.
Lastly, the INDEX function returns the value from only column 3 that results from the split.
The end result is that we’re able to get only the last item from the split, which is Douglas.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
Google Sheets: How to Split Text into Rows
Google Sheets: How to Split Date & Time
Google Sheets: How to Use SPLIT with Multiple Delimiters
