Sishir Roy
In Google Sheets, a cell may occasionally include both a numeric number and a non-numeric character. You do not want the non-numeric character in your cell. Therefore, you may manually edit the cell. However, it might be difficult and even impossible to manually change every cell to remove the last character when the dataset is large. In this article, we will demonstrate some easy ways to remove the last character from a string in Google Sheets.
4 Easy Ways to Remove Last Character from String in Google Sheets
The worksheet below will be used to demonstrate the examples.
It contains animals’ average body temperatures. The temperature here is not a numeric value only, it has a non-numeric character at the end of every cell. Here, we will show you 4 easy ways to remove the last character from a string in Google Sheets:
1. Combining LEFT and LEN Functions to Remove the Last Character
You can extract characters from a string using the LEFT function in Google Sheets. The LEFT function returns a certain number of characters from the left. Once you have determined the total number of characters in a string, you can use the LEFT function to eliminate the final character. In our case, we have determined the total number of characters in a string using the LEN function.
Steps:
- Select a cell in Google Sheets where you want your desired result. We selected Cell D5.
- Now type the formula in Cell D5 and press the Enter button:
=LEFT(C5, LEN(C5)-1)Where C5 is the cell from which you want to remove the last character.
Formula Breakdown:
- LEN(C5)
First, the LEN function will count the number of characters in the cell.
- LEFT(C5,LEN(C5)-1)
Then, the LEFT function will return the number of characters from the left. Because of LEN(C5)-1; it will return one less character from the left. As the non-numeric character F is in the last place, it will extract it.
- You can now drag the Fill Handle icon to the end or just double-click on it to remove the last character from all other cells.
- Now all the cells have numeric values only.
2. Merging MID and LEN Functions to Delete the Last Character
We can also remove the last character from a string in Google Sheets using the MID function. It follows the same logic as the LEFT function. The only difference is that the LEFT function extracts characters from the left of a string, whereas the MID function extracts characters from the middle of a string.
Steps:
- Choose the cell in Google Sheets where you want the desired outcome to appear. We chose Cell D5.
- Now, type the formula in cell D5 and press the Enter button.
=MID(C5,1,LEN(C5)-1)
Where C5 is the cell from which you want to remove the last character.
Formula Breakdown:
- LEN(C5)
First, the LEN function will count the number of characters in the cell.
- MID(C5,1,LEN(C5)-1)
Then, the MID function will extract the number of characters. The MID function will ask you the starting position from which it will start counting the number of characters. Because of LEN(C5)-1; it will return one less character from the starting point. As we choose the starting position from 1 and the non-numeric character F is located at the end of the string, it will extract the non-numeric character.
- To delete the final character from all other cells, double-click on the Fill Handle icon or just drag it to the end.
- You will get your desired result.
Read More: Remove Everything after Character Using Google Sheets Formula
3. Combining REPLACE and LEN Functions to Get Rid of the Last Character
Another way to remove the last character from a string in Google Sheets is by using the REPLACE function. We can replace the last character from a string with a blank character using this method.
Steps:
- Choose a cell in Google Sheets where you want your desired result. In our case, we chose Cell D5.
- Now, type the below formula in your chosen cell and hit the Enter button.
=REPLACE(C5,LEN(C5),1,"")Where Cell C5 is the cell from which we want to trim the last character.
Formula Breakdown:
- LEN(C5)
First, the LEN function will count the number of characters in the cell.
- REPLACE(C5,LEN(C5),1,””)
Then, the REPLACE function will replace the part of a text string with a different text string. The REPLACE function will ask you the position from where the replacement will start. Because of LEN(C5); it will start the replacement from the last character. It will also ask you how many characters are in the text to be replaced as well as what text is going to replace the original text. We have replaced the last character with blank text.
- Now double-click on the Fill Handle icon or just drag it to the end to remove the last character from all other cells. You will obtain the outcome you want.
- You will obtain the outcome you want.
Read More: How to Remove Text after Character in Google Sheets (5 Methods)
4. Using Google Apps Script to Eliminate the Last Character
In Google Docs, Sheets, Slides, and Forms, you may use Google Apps Script to add a personalized menu, dialog, and sidebar. With the aid of the Apps Script tool, Google Sheets enables users to build custom functions according to their unique needs. We can use this tool to remove the last character from a string in Google Sheets.
Steps:
- Click on the Extensions tab from the menu bar and select Apps Script.
- To eliminate the final character from the string, enter the following code. Make sure you have given the entire range from which you want to delete the final character.
function RemoveLastCharacter() {
const sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
const rng = sh.getRange('C5:C10' + sh.getLastRow())
const data = rng.getValues()
data.map(r => r[0] = r[0].substring(0, r[0].length - 1))
rng.setValues(data)
}
- Click on the Save icon at the top to save the code.
- Click on the Run icon to activate the code in the current worksheet.
- The code has run successfully and we get our desired output.
Read More: How to Remove First Character in Google Sheets
Conclusion
These are the few techniques you may use to remove the last character from a string in Google Sheets. If you want to remove any number of characters from the last, you can just simply modify these methods to get your desired result. Please feel free to leave any questions or suggestions in the comments section below. Visit our site Officewheel.com to explore more.
