When working with data in Google Sheets, sometimes you may have a need to remove the last character from a string (or remove the last 2 or 3 or 5 characters).
This can easily be done using some basic formulas in Google Sheets.
In this tutorial, I will show you how to use formulas to remove the last character from a string in Google Sheets.
Remove Last Character from a String Using the LEFT Function
With the LEFT function in Google Sheets, you can specify the number of characters that you want to extract from a string in a cell.
So if somehow you can figure out how many characters are there is a cell, you can use the LEFT function to get all the characters except the last one.
Let me show you how to do this.
Suppose you have a dataset as shown below and you want to get the numbers but not the alphabet at the end.
Below is the formula will so this:
=LEFT(A2, LEN(A2)1)
The above formula uses the LEN function to get the total number of characters in the referenced cell. It then uses the LEFT function to extract all the characters from the referenced cell except the last one (using the result of the LEN function).
In case you want to remove any other number of characters from the end (say 3 characters or 5 characters), you can tweak the above formula itself.
Below is a formula that will remove the last three characters from a string in Google Sheets:
=LEFT(A2, LEN(A2)3)
Once you have the result in one cell, you can copy the formula for all the cells to get the result.
Also, once done, you can convert the formulas to values (using Paste Special), in case you no longer need the formula.
Remove Last Character from a StringÂ Using the MID Function
Another way to remove the last character is by using the MID function.
While the LEFT function can extract the specified number of characters from the left of a string, the MID function can allow you to extract the specified number of characters from the middle of a string.
Suppose you have a dataset as shown below and you want to get the numbers but not the alphabet at the end.
Below is the formula that will do this:
=MID(A2,1,LEN(A2)1)
The above formula extracts all the characters beginning from the first character till the second last character. And we know how many characters are there because of the LEN function.
Remove Last Character from a String Using the REPLACE function
Another supereasy way to do this is by using the REPLACE function.
With the REPLACE function, you can replace the last character with a blank character (which is equivalent to removing it).
Below is the formula that will do this.
=REPLACE(A2,LEN(A2),1,"")
The above formula goes to the last character and replaces it with “”.
Again, the LEN function is used to know the total number of characters that are there in the string.
So these are three easy formulas you can use to remove the last character from a string in Google Sheets.
Hope you found this Google Sheets tutorial useful!
You may also like the following Google Sheets tutorials:
 Using IFS Function in Google Sheets to Test Multiple Conditions
 Using Query Function in Google Sheets
 The Ultimate Guide to Google Sheets VLOOKUP Function (with Examples)
 Capitalize First Letters in Google Sheets (using Formula)
 How to Convert Text to Numbers In Google Sheets
Google Sheets and Microsoft Excel Expert.

Sumithttps://productivityspot.com/author/sumitbansal23/

Sumithttps://productivityspot.com/author/sumitbansal23/

Sumithttps://productivityspot.com/author/sumitbansal23/

Sumithttps://productivityspot.com/author/sumitbansal23/
1 thought on “Remove Last Character from a String in Google Sheets (or Last N Characters)”
Very useful. Thank you!
Comments are closed.