Google Sheets Remove Last Character: Step-by-Step Guide (3 Easy Methods)

Sumit, Cindy Wong

Last updated November 7, 2023

Are you looking for a Google Sheets remove last character step-by-step guide? When working with data in Google Sheets, sometimes you must 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.

This Article Covers:

Remove the Last Character from a String Using the LEFT Function

I often use the LEFT function in Google Sheets because it lets me specify the number of characters I can extract from a string in a cell.

For example, if I want to figure out how many characters there are in a cell, I would use the LEFT function to determine all the characters except the last one. This is helpful, especially when you need to extract specific characters from the beginning of a cell for further analysis.

Let me show you how to do this with a quick example.

In my example, I will use the following dataset to extract the numbers but not the letters at the end.

To do this, I would simply use the formula below:

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

As you can see from the above formula, I have used the LEN function to determine the total number of characters in the referenced cell. I then used the LEFT function to extract all the characters (numbers) from the referenced cell except the last one (using the result of the LEN function).

If you want to remove another number or character from the end (for example, the last 3 characters), you can tweak the above formula. Let me show you using the formula below to remove the last three characters from a string in Google Sheets:

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

One of the reasons why I like to use this formula is because once you have the result in a cell, you can copy the formula for all the cells to get your desired result. Additionally, once done, you can convert the formulas to values (using Paste Special) if you no longer need the formula, giving you a win-win situation.

Remove the Last Character from a String Using the MID Function

Another way to remove the last character is by using the MID function. I like to use this function because it helps me to extract a substring from a given text or string. It also lets me specify the length and starting position of the substring I want to extract.

The MID function differs from the LEFT function because when using the LEFT function, I can extract the specified number of characters from the left of a string. The MID function, on the other hand, lets you extract the specified number of characters from the middle of a string.

Let’s look at an example of using the MID function to extract the numbers but not the letters at the end.

Here, I have used the following formula:

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

The above formula extracts all the characters from the first character to the second last character. Furthermore, it also tells me how many characters there are because I have used the LEN function.

Remove the Last Character from a String Using the REPLACE Function

Another super-easy way to do this is by using the REPLACE function.

I like to use the REPLACE function because it lets me replace the last character with a blank character (which is equivalent to removing it).

To do this, I used the formula below:

=REPLACE(A2,LEN(A2),1,"")

The above formula replaces the last character with double speech marks (“”).

Here, I have used the LEN function to determine the total number of characters that are there in the string.

Frequently Asked Questions

Why Use the LEFT Function?

I use the LEFT function to retrieve a selected number of characters, starting from the left side of an Excel cell. It is a helpful function to learn because it lets you extract pieces of data from a string of characters in one cell.

Why Use the MID Function?

I use the LEN function because it helps me determine the length of a text or a string in Excel. It’s a helpful function because it can be used for various tasks, including validating data, setting up conditional formatting, or simply understanding the content size within a cell.

Why Use the REPLACE Function?

I use the REPLACE function because it lets me modify or replace specific portions of a text string within a cell. This function comes in handy when making changes to my data, such as updating information, replacing certain characters, or reformatting text without manually editing each cell. It’s a great time-saving and efficient way to manipulate text within my spreadsheets.

It’s also easy to learn and master, so it’s definitely one Excel function you want in your arsenal.

Wrapping Up

And there you have it. You can use these three easy formulas to remove the last character from a string in Google Sheets.

Removing the last character from a string in Google Sheets is crucial for anyone who wants to clean up and format data accurately. When working with data, there are always instances when there are unwanted characters, and unless you clean up your data, you are left with unnecessary characters that can cause issues in your calculations, sorting, or even visual presentation.

By using the formulas above and removing the unwanted characters, I ensure that my data is consistent, making it easier to analyze my data in Google Sheets.

I hope you found this Google Sheets remove last character tutorial useful, improving the quality and reliability of your spreadsheets.

You may also like the following Google Sheets tutorials: