Getting the last element of cells is often necessary. For instance, we may need to separate different portions of a person’s name from a single string separated by a comma or space. We have multiple different ways in Google Sheets for performing such tasks. This article serves you a comprehensive guide to split and get last element of cells in Google Sheets with 4 suitable methods.

## A Sample of Practice Spreadsheet

You can download the spreadsheets from the link below. The spreadsheets contain a dataset we use here. After downloading you can practice on your own as we demonstrate here.

## 4 Suitable Methods to Split Cells and Get Last Value in Google Sheets

The following dataset contains** ID**, **First Name** and **Last Name** of persons in a single cell separated by a comma. Using this dataset we are going to demonstrate three methods of **splitting a cell** and extracting the **Last Name** only.

### 1. Using SPLIT Function

**The SPLIT function** splits cells into columns based on a given delimiter. However, we can get the last column only by employing** INDEX **and **COUNTA** functions with it.

📌** Steps:**

- First of all, we select cell
**C5**. - Then we write down the formula below in the formula bar of cell
**C5**.

`=INDEX(SPLIT(B5,","),0,COUNTA(SPLIT(B5,",")))`

** **

__Formula Breakdown__

**SPLIT(B5,”,”)**

The **SPLIT **function splits the text of the cell **B5 **into columns by using **comma** as delimiters.

**COUNTA(SPLIT(B5,”,”)**

The **COUNTA** function returns the number of columns of the splitting text and the return value is used as an argument of the **COUNTA **function.

**INDEX(SPLIT(B5,”,”),0,COUNTA(SPLIT(B5,”,”)))**

Finally, the **INDEX **function returns the last column of the splitting text.

- As a result, we get the
**Last Name**of the first person in the selected cell as follows. - Afterward, we use the
**fill handle**tool to get the**Last Name**of subsequent rows.

** **

- Finally, we get the following result where only the
**Last Name**is extracted by splitting the corresponding cell.

** **

**Read More: ****How to Split Text to Columns Using Formula in Google Sheets**

### 2. Applying the REGEXEXTRACT Function

We can also split and get last element of a cell by applying **the REGEXEXTRACT function**. We have to write a regular expression based on text when using this function.

📌** Steps:**

- Initially, we select cell
**C5**as earlier.

- Then we write down the formula below in the formula bar of cell
**C5**.

`=REGEXEXTRACT(B5,"[^,]+$")`

** **

- Consequently, we get the
**Last Name**of the first person in the selected cell as indicated. - Next, we use the
**fill handle**tool to get the**Last Name**for other rows.

- Once we complete the fill handle operation we got the following result.

**Read More: ****[Solved!] Split Text to Columns Is Not Working in Google Sheets**

**Similar Readings**

**How to Split View in Google Sheets (2 Easy Ways)****Split String into Array in Google Sheets (3 Easy Methods)****How to Split Address in Google Sheets (3 Easy Methods)**

### 3. Combining TRIM and LEN Functions

There are a lot of functions in Google Sheets. We can use a few of them combinedly to get the desired result. In this method, we demonstrate how to use a combination of various functions to split and get the last element of cells.

📌** Steps:**

- At the very beginning, we select cell
**C5**.

- Next, we insert the formula below in the formula bar.

`=TRIM(RIGHT(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))),LEN(B5)))`

__Formula Breakdown__

**LEN(B5)**

**The LEN function** finds the length of given cell **B5**.

**REPT(” “,LEN(B5))**

This returns the repetition of blank string for the given number of times with the help of **the REPT function**.

**SUBSTITUTE(B5,”,”,REPT(” “,LEN(B5))**

**The SUBSTITUTE function** searches for comma in the given text and then replace them with repetitive blank spaces.

**RIGHT(SUBSTITUTE(B5,” “,REPT(” “,LEN(B5)))**

**The RIGHT function** returns a string that results from the **SUBSTITUTE** function from the right side.

**TRIM(RIGHT(SUBSTITUTE(B5,” “,REPT(” “,LEN(B5))),LEN(B5)))**

Finally, **the TRIM function** returns the targeted **Last Name** only from the given text by removing whitespaces.

- As a result, we get the
**Last Name**as expected in the selected cell.

- After that, we use the fill handle tool to get the
**Last Name**in other rows.

- Completing the fill handle operation gives us the following result.

**Read More: ****How to Split Cell by Comma in Google Sheets (2 Easy Methods)**

### 4. Merging RIGHT and FIND Functions

We can perform the task of splitting and getting the last element on some occasions by merging the **RIGHT **and **FIND** functions.

📌** Steps:**

- Firstly, we select cell
**C5**as earlier.

- Then we insert the formula below in the formula bar.

`=RIGHT(B5,LEN(B5)-FIND(",",B5))`

__Formula Breakdown__

**FIND(“,”,B5)**

The **FIND **function finds the position of the first **comma **within the text of given cell **B5**.

**LEN(B5)-FIND(“,”,B5)**

This returns the number of characters we want to get from the right side of the text of the given cell.

**RIGHT(B5,LEN(B5)-FIND(“,”,B5))**

The **RIGHT** function returns the substring of the text of cell **B5** based on the given condition.

- Here, we get both
**First Name**and**Last Name**instead of getting the**Last Name.**

- However, we can still split cells and get the
**Last Name**only using the same formula if the cells don’t contain person’s**ID**like below which means if there were only two data separated by one comma we could get perfect result. - Finally, we drag down the fill handle tool to get the
**Last Name**for all the rows.

- As a result, we get the
**Last Name**in all the rows like the methods we have already discussed.

**Note:**

This method is not convenient for strings that contain more than one comma. If there is more than one comma, it would return the whole string part after the first comma which might not be our desired result.

**Read More: ****How to Split String Using Apps Script in Google Sheets**

## Things to Remember

- Be careful about the use of delimiters.
- Try to use the
**REGEXEXTRACT**function whenever possible because you can customize it to your needs.

## Conclusion

In conclusion, splitting cells and getting the last element may be useful in some cases. I believe from now on you can easily split and get last element of cells in Google Sheets. Furthermore, If you have any questions regarding this article feel free to comment below and I will try to reach out to you soon. Visit our website **OfficeWheel** for the most useful articles.