LEFT and LEN Excel Formula: How to Use Them Together

Formulas & functions · Updated November 18, 2026

To remove the last character from text in Excel, combine LEFT and LEN like this: =LEFT(A2,LEN(A2)-1). With the text in A2, LEN counts its characters and LEFT returns all but the final one; use an IF check if the cell might be blank.

What LEFT and LEN do

LEFT and LEN are separate Excel functions that solve different parts of a text task. LEFT returns characters from the beginning of a text value. LEN counts the characters in that value, including spaces and punctuation. Together, they let you ask for a specific number of characters based on how long the text is.

With text in A2, this formula returns the first four characters:

With a value such as “Notebook” in A2, the formula returns “Note”.

With text in A2 and the desired character count in B2, this formula returns the first B2 characters:

For instance, if A2 contains “Notebook” and B2 contains 4, the result is “Note”. These basic uses help explain the combined pattern: LEFT needs a character count, and LEN can calculate that count from the cell’s contents.

The LEFT LEN pattern for removing the final character

To understand the common formula, read it from the inside out. LEN(A2) returns the number of characters in A2. Subtracting 1 gives the number of characters before the final one. LEFT then returns that many characters from the beginning.

Suppose A2 contains AB-123. LEN returns 6, so the requested LEFT count is 5. The result is AB-12. If A2 contains Report., the result is Report. The formula does not know whether the last character is a period, a letter, or a digit; it removes the final character regardless.

This approach is useful when the last character is consistently unwanted, such as a trailing punctuation mark, a code marker, or an extra symbol. It is not a way to remove a particular character wherever it appears. For example, it removes the final digit from a code but leaves any matching digit earlier in the text unchanged.

Worked examples and variations

Here are practical versions of the pattern. Each assumes the source value is in A2 unless stated otherwise.

  • With a nonblank value in A2, remove exactly one final character using =LEFT(A2,LEN(A2)-1). If A2 is INV-204X, the result is INV-204.

  • With a value in A2 and the number of characters to remove in B2, remove that many characters from the end using =LEFT(A2,LEN(A2)-B2). If A2 is INV-204X and B2 is 2, LEN returns 8 and the result is INV-204.

  • With a value in A2 that may be blank, return an empty result for a blank cell and otherwise remove its final character using =IF(A2="","",LEFT(A2,LEN(A2)-1)). For a blank A2, this returns a blank-looking result rather than an error.

The second variation is useful for a variable number of characters, but check that B2 is not greater than the text length. If the subtraction makes LEFT’s character count negative, Excel returns #VALUE!. If B2 is zero, the formula returns the original text. A negative B2 increases the requested count, and LEFT returns the full text when that count exceeds its length.

Remove a known suffix or clean a text column

If every value ends with a suffix of a fixed length, subtract that length instead of 1. For example, if each value in A2 ends in a three-character marker that should be discarded, use this formula:

With ITEM-47XYZ in A2, LEN returns 10 and LEFT returns the first 7 characters, ITEM-47. This method depends on the suffix always being three characters long. If some rows have different suffix lengths, a fixed subtraction can cut off too much or too little.

For a column of values, enter the chosen formula in the first result row, such as B2, and fill it down alongside the source data. In Microsoft 365 for Windows, you can select the formula cell and drag its fill handle down, or select the destination range and use Home > Fill > Down. Check several results, especially the first and last rows, before using the cleaned values elsewhere.

Formulas return results without changing the original cells. If you need to replace the source values, first make sure the results are correct, then copy the results and use Paste Special > Values in the destination. Keeping the original column until you have checked the output makes it easier to recover from an incorrect rule.

Spaces, blanks, and common errors

LEN counts spaces. If A2 contains cat with a space after the word, the formula removes that trailing space—not the letter “t”. This can make a result appear unchanged on screen even though its contents differ. If the goal is to remove extra spaces, identify whether the spaces are at the beginning, end, or between words before choosing a cleanup method.

A blank cell is a special case for the one-character removal formula. LEN returns 0, so subtracting 1 produces a negative character count and LEFT returns #VALUE!. The IF version shown above avoids that result by checking whether A2 is blank first. It also returns an empty string when A2 contains a formula whose result is empty text.

Other common causes of unexpected results include:

  • Wrong source reference: Check that the formula points to the cell containing the text, not a nearby header or output cell.

  • Unintended final character: The formula always removes the last character, even if some records do not have the marker you expected.

  • Numeric values and leading zeros: LEFT works with text, and Excel can coerce numbers to text, but a value stored as a number may already have lost leading zeros. Store identifiers such as postal codes or product codes as text when those zeros matter.

  • Argument separators: The examples use commas. Excel installations configured for some regional settings may require semicolons between arguments; use the separator expected by your installation if Excel rejects an otherwise valid formula.

When LEFT and LEN are not the best choice

The combined formula is a good fit when the position to keep is defined by the total text length—for example, “keep everything except the last character.” If the rule is instead “remove a particular character wherever it occurs,” LEFT and LEN alone are not the right tool. Excel’s Find and Replace can substitute or remove known text, while SUBSTITUTE can replace specific text within a value.

If the unwanted text is a known suffix and you want to remove it only when it is actually present, use a method that checks the ending before trimming. Blindly subtracting a fixed number of characters can damage entries that do not have that suffix. Likewise, if you need text before or after a delimiter that can move between rows, a delimiter-based approach is usually clearer than calculating a position from the overall length.

Do not confuse LEFT with LEN: LEFT extracts text, while LEN only reports a character count. Neither function edits the original cell. The formula produces a separate result, and the source remains intact unless you deliberately replace it with pasted values.

Entering and checking the formula

In Excel for Microsoft 365 on Windows, select the result cell, type the formula, and press Enter. Replace A2 with the correct source reference for your sheet. To apply it to more rows, fill the formula down. Excel adjusts a relative reference such as A2 to A3, A4, and so on as it fills the formula.

On Excel for Mac and Excel for the web, the formula itself works the same way. The ribbon layout or available fill controls can differ slightly by platform, but you can enter the formula in a cell or the formula bar and fill it down. If your regional settings use a different list separator, change the commas to the separator Excel expects.

Before relying on the results, compare a few rows manually. Include a short value, a typical value, a blank if blanks are possible, and an entry with a trailing space or an unusual final character. That quick review confirms that the rule matches the data rather than merely producing a plausible-looking output.

Frequently Asked Questions

What does LEFT(LEN()) do in Excel?
LEFT returns characters from the start of text, while LEN counts the text’s characters. Used together, they can calculate how many characters to return, such as all characters except the last one.

How do I remove the last character in Excel?
With the text in A2, use =LEFT(A2,LEN(A2)-1). It removes one final character, whatever that character is.

How do I remove the last two characters?
With the text in A2, use =LEFT(A2,LEN(A2)-2). It removes two characters from the end, provided the text is long enough.

Why does my formula return #VALUE!?
A blank source cell can make LEN(A2)-1 negative, which LEFT cannot use as its character count. A negative count can also result if you subtract more characters than the text contains. Add a blank check or verify the requested count.

Does LEN count spaces in Excel?
Yes. Spaces count as characters, including a space at the end of a cell. That means removing the last character may remove a trailing space rather than a visible letter or symbol.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.