Remove or Replace Last Character from a String in Google Sheets

To remove or replace the last character from a string in Google Sheets, you can utilize either a formula or the Find and Replace command.

When using a formula, several options are available, including the REGEXREPLACE function, or combinations such as LEN + REPLACE, LEN + LEFT, or LEN + MID.

It’s essential to note that the formula doesn’t modify the original text; instead, it executes the operation and displays the result in another cell or cell range.

One advantage of using the menu command is its ability to modify the existing data directly. We will employ a regular expression within the Find dialog box to identify the last character and replace it with either null or a character of your choice.

Removing or Replacing the Last Character Using Find and Replace in Google Sheets

In the following example, we have fruit names like “apples,” “mangos,” “bananas,” and “grapes” in cells C2 to C5.

Let’s explore how to remove the last characters from these fruit names using the Find and Replace dialog box in Google Sheets. Follow these step-by-step instructions:

  1. Select cells C2 to C5.
  2. Click on Edit > Find and Replace to open the dialog box. Alternatively, you can use the Google Sheets keyboard shortcuts Ctrl + h in Windows or Command + Shift + h on Mac.
  3. In the field next to “Find,” enter .$
  4. Leave the field next to “Replace with” empty.
  5. Choose “Specific range” from the drop-down next to “Search.”
  6. Check both “Search using regular expressions” and “Match case” (note: “Match case” may be automatically selected when choosing “Search using regular expressions”).
  7. Click the “Replace all” button.
  8. Click “Done.”
"Find and replace last character in Google Sheets

This is the fastest method to remove the last character from a list of strings in Google Sheets.

If you wish to replace the last character with another character, enter that character or characters in the “Replace with” field.

Formula Options

As I mentioned at the beginning of this tutorial, there are several formula options available to remove the last character in a string.

Utilizing the REGEXREPLACE Function to Remove or Replace the Last Character

=REGEXREPLACE(C2, ".$", "")
Non-array formula to remove the last character in a string

This follows the syntax REGEXREPLACE(text, regular_expression, replacement):

  • text: C2
  • regular_expression: .$ (. matches any character except for line terminators, and $ asserts position at the end of a line)
  • replacement: “”

This formula removes the last character from the string in cell C2.

If you want to replace the last character, specify the replacement in the argument. For example, using “_2” as the replacement will return “apple_2”.

To remove or replace the last characters in the range C2:C5, use the above REGEXREPLACE as an array formula:

=ArrayFormula(REGEXREPLACE(C2:C5, ".$", ""))
Array formula to remove the last character in a list of strings

Utilizing LEN and REPLACE Functions Combo to Remove or Replace the Last Character

This is the second formula option to remove or replace the last character in a string or a list of strings.

The following combination will remove the last character in the string in cell C2:

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

The LEN function returns the number of characters in the string in cell C2, which corresponds to the position of the last character.

The REPLACE function then replaces the character in that position with a new text, specified here as "".

Syntax: REPLACE(text, position, length, new_text)

  • text: C2
  • position: LEN(C2)
  • length: 1 (one character)
  • new_text: “”

If you want to replace the last character, specify the replacement in the argument. For example, using “_2” as the new_text will return “apple_2”.

To use it in an array, for instance in the range C2:C5, apply the following formula:

=ArrayFormula(REPLACE(C2:C5, LEN(C2:C5), 1, ""))

Utilizing LEN and LEFT Functions Combo to Remove the Last Character

Here is the widely used combo to remove the last character from a string in Google Sheets as well as in Excel:

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

The LEFT function returns LEN(C2)-1 characters from the left of the string.

Syntax: LEFT(string, [number_of_characters])

To apply this to a list, for example, C2:C5, use it as follows:

=ArrayFormula(LEFT(C2:C5, LEN(C2:C5)-1))

Utilizing LEN and MID Functions Combo to Remove the Last Character

This combo is similar to LEN and LEFT.

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

Syntax: MID(string, starting_at, extract_length)

Here, the MID function returns LEN(C2)-1 characters starting at 1.

To apply this to a list, for example, C2:C5, use it as follows:

=ArrayFormula(MID(C2:C5, 1, LEN(C2:C5)-1))

Resources

Here are some other tutorials covering similar topics, including character replacement and additional tips and tricks.

  1. Regex to Replace the Last Occurrence of a Character in Google Sheets
  2. How to Group a Column Based on First Few Characters in Google Sheets
  3. How to Remove the First N Characters in Google Sheets
  4. How to Check First N Characters Are Caps or Small in a List in Google Sheets
  5. Make Duplicates to Unique by Assigning Extra Characters in Google Sheets
  6. Insert Delimiter into a Text After N or Every N Character in Google Sheets
  7. Remove Repeated Characters from the End of Strings in Google Sheets
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.