If you need to remove the last character from a string in your spreadsheet, there are a number of ways you can do this in Google Sheets.
There are a number of formulas you can create that will remove the last N characters depending on how many characters you want to remove from your data.
In this tutorial, I will show you several methods to remove the last character from a string in Google Sheets
Table of Contents
Remove Last Character with LEFT and LEN Functions
You can combine the LEFT and LEN functions to remove the last character from a string.
The LEFT function returns a substring starting at the beginning of a string according to the specified number of characters.
The LEN function returns the length of a string by counting its characters.
So to combine these two functions to remove the last character the syntax would be:
=LEFT(string,LEN(string) – number_of_characters)
- string – this is the string you want to remove the last Nth character from
- number_of_characters – this is how many characters you want to remove from the end of the string
Here is an example of how this is used in a spreadsheet:
This formula works because the LEN counts the number of characters in your string, and then subtracts out how many characters you want to remove. Then, the LEFT function starts at the beginning of your string and returns this number of characters.
Remove Last Character with MID and LEN Functions
You can also use the MID formula in combination with LEN to remove the desired number of characters. MID will return a segment of a string when given the starting character and number of characters to extract.
To use it in combination with LEN to remove characters from the end of the string, you would use it with this syntax:
=MID(string,1,LEN(string) – number_of_characters)
This is almost the same as the previous method but your second argument in the function is a 1 to indicate that you want to start at the first character of the string.
Here is how this would look in a spreadsheet:
This works almost the same way as the previous method. It finds the length of your string, subtracts the number of characters you want to remove, and then MID starts at the first character and returns that number of characters.
Remove Last Character with REPLACE and LEN Functions
The last option I will cover is using REPLACE in combination with LEN.
REPLACE will remove a part of a string with a different text string. What we will be doing is replacing the end of the string with a blank character, which will be the same as removing it.
To combine these functions we will be using this syntax:
Here is how this will look when used in a spreadsheet:
As you can see, we have a few options to remove characters from the end of the string. The key is combining functions with the LEN function to be able to figure out which characters to remove.
Try replicating each of these formulas in your own spreadsheet to get a firm understanding of how each function works.
More Google Sheets Tutorials:
If Cell Contains
How to Extract Numbers of Text from a String
How to Use the LEN Function