How to Remove the Last Character from a Text String in Excel

When working with data cleanup in Excel, a frequent challenge is removing unwanted characters from the end of a text string. Whether you need to strip trailing punctuation, delete a placeholder icon like an asterisk (*), or clean up corrupted system exports, Excel offers multiple ways to tackle the job.

Depending on your version of Excel, you can use a traditional, highly compatible nested formula or harness the modern power of Regular Expressions. Below, we explore the best methods to remove the last character or a specific number of characters from text strings, including how to make these processes conditional.

Pro Tip: If your primary goal is simply to clean up trailing or leading spaces from a text string, the TRIM function is a much faster and easier alternative. It normalizes spacing between words and strips out extra spaces in a single step.

Method 1: The Traditional LEFT and LEN Formula

The most reliable approach across all versions of Excel combines the LEFT and LEN functions. This method calculates the length of the string and extracts everything except the specified number of trailing characters.

• The LEFT function returns a specified number of characters starting from the left side of a text string.
• The LEN function measures the total character length of a text string.

How to Remove a Single Character

To remove exactly one character from the right side of a string, nest the LEN function inside LEFT and subtract 1:

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

How it works step-by-step:
Using the example cell containing "Paris, France*":

1. LEN(B5) counts the total characters, returning 14.
2. The formula subtracts 1 from the total (14 - 1), which equals 13.
3. LEFT(B5, 13) extracts the first 13 characters from the left.
4. The final output is "Paris, France", successfully dropping the asterisk.

How to Remove the Last N Characters

You can easily scale this formula to drop multiple characters by changing the subtracted value. The generic structure where n represents the number of characters to drop is:

=LEFT(A1,LEN(A1)-n)

For instance, if your text ends with three trailing asterisks (***), adjust the formula to subtract 3:

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

Alternative: Using the REPLACE Function

Another backward-compatible option for older Excel versions utilizes the REPLACE function. The syntax to remove the last n characters looks like this:

=REPLACE(A1,LEN(A1)-n+1,n,"")

This replaces n characters at the very end of your string with an empty string (""), achieving the exact same result.

Method 2: The Modern REGEXREPLACE Function

If you are running the latest, modern versions of Excel, you have access to built-in Regular Expression capabilities. The REGEXREPLACE function provides a cleaner, more flexible syntax that handles text manipulations within a single function call.

How to Remove a Single Character

To drop the final character from a cell regardless of what that character is, use the following pattern:

=REGEXREPLACE(B5,".$","")

Breaking down the regex pattern (".$"):

• . (Dot): A wildcard that matches any single character.
• $ (Dollar Sign): An anchor that forces the match to occur strictly at the end of the text string.
• "" (Replacement): Tells Excel to swap that matched final character with nothing.

How to Remove the Last N Characters

Regex scales gracefully using quantifiers. To specify an exact number of characters to strip, use the {n} syntax. For example, to match and delete exactly three trailing characters, write:

=REGEXREPLACE(B5,".{3}$","")

The pattern .{3}$ tells Excel to find exactly three instances of any character (.{3}) located at the very end of the text string ($), replacing them with an empty string.

Making Your Cleanup Formulas Conditional

A major real-world issue with blind character removal is accidentally chopping off vital text from cells that are already clean. To prevent this, you must build conditional logic into your spreadsheet formulas so they only modify text when specific trailing characters exist.

Method A: Conditional Check with IF + RIGHT

If you are using traditional formulas, wrap your cleanup step inside an IF function combined with a RIGHT function check:

=IF(RIGHT(B5,1)="*",LEFT(B5,LEN(B5)-1),B5)

This logic reads: If the single furthest-right character is an asterisk, apply the LEFT + LEN removal formula. Otherwise, return the original text untouched.

Method B: Conditional Check with REGEXREPLACE

With regular expressions, you do not need to wrap your formula inside a separate IF statement. Instead, you build the conditional check directly into your regex pattern.

To strip a trailing character only if it is a literal asterisk, use:

=REGEXREPLACE(B5,"*$","")

Why the backslash? In regular expressions, the asterisk (*) is a reserved metacharacter meaning “match zero or more times”. To tell Excel you want to look for a literal, visual asterisk, you must escape it by placing a backslash () right before it.

If the string does not end with an asterisk, the regex match fails entirely, and REGEXREPLACE automatically leaves your original text unmodified.

Choosing the Best Solution for Data Cleanup

Choosing between these methods comes down to balancing compatibility with processing power:

While traditional text functions get the job done everywhere, mastering modern regex tools provides immense flexibility, giving you pinpoint control over data cleanup workflows without building over-complicated formulas.