Remove Last Character from a String in Google Sheets (or Last N Characters)

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.

Data from which last character needs to be removed

Below is the formula will so this:

=LEFT(A2, LEN(A2)-1)

Using LEFT formula to remove the last character

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.

Data from which last character needs to be removed

Below is the formula that will do this:

=MID(A2,1,LEN(A2)-1)

Using MID formula to remove the last character

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 super-easy 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,"")

Using REPLACE formula to remove the last character

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:

1 thought on “Remove Last Character from a String in Google Sheets (or Last N Characters)”

Leave a Comment