Excel: Split Text and Get Last Item

---

The TEXTSPLIT function in Excel can be used to split text based on a specific delimiter.

To get the last item that results from this function, you can use the following syntax:

=CHOOSECOLS(TEXTSPLIT(A2, " "), -1)

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 Chad Mike Douglas then this formula will return Douglas.

The following example shows how to use this formula in practice.

Example: How to Split Text and Get Last Item in Excel

Suppose we have the following column of names in Excel:

Suppose we would like to split the names based on where a space occurs and then get only the last item that results from the split.

We can type the following formula into cell B2 to do so:

=CHOOSECOLS(TEXTSPLIT(A2, " "), -1)

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 space occurs and then only returns the last item from the split.

For this particular example, the last name of each person in column A is returned.

How This Formula Works

=CHOOSECOLS(TEXTSPLIT(A2, " "), -1)

Here is how this formula works:

First, the TEXTSPLIT function splits the text in cell A2 based on where the space occurs.

For example, the name Andy Bernard is split into one column that contains Andy and a second column that contains Bernard.

Next, the CHOOSECOLS function selects only the last column from the output.

The end result is that we’re able to get only the last item from the split, which is Bernard.

Note: You can find the complete documentation for the CHOOSECOLS function in Excel .