Laku
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.
