How to Remove Last Digit in Excel
To remove the last digit or character in Excel, use =LEFT(A1, LEN(A1)-1), which keeps everything except the final character. To remove the last digit from a number and keep a true number as the result, use =TRUNC(A1/10) instead. Which one you reach for depends on whether your value is text or a number, and that single distinction trips up more people than the formulas themselves.
This guide covers every reliable method, confirms each formula works identically on Windows and Mac, and ends with fixes for the two problems that catch almost everyone: lost leading zeros and a result that won’t behave like a number.
Remove the last character with LEFT and LEN
The most common approach combines two text functions. The LEFT function returns characters from the start of a string, and the LEN function counts how many characters a cell contains. Put them together and you can return “everything but the last one.”
Assuming your value is in cell A1:
=LEFT(A1, LEN(A1)-1)LEN(A1) counts the total characters, you subtract 1, and LEFT returns that many characters from the left. If A1 holds 12345, the result is 1234. If A1 holds Product9, the result is Product. The formula does not care whether the content is a number or text, which makes it the most versatile method on this page.
To apply it down a column, enter the formula once and double-click the small fill handle (the square at the bottom-right corner of the selected cell) to copy it down the length of your data.
Remove the last N characters
The same pattern scales to any number of characters. Replace the 1 with the count you want to drop. To remove the last three characters:
=LEFT(A1, LEN(A1)-3)If A1 holds Invoice-2026, this returns Invoice-. This is the standard way to strip a fixed-length suffix such as a currency code, a file extension, or a trailing unit label. To keep the end of the string and remove characters from the front instead, use the RIGHT function; to pull a chunk from the middle, use the MID function. The full family is collected in our guide to Excel text functions.
One caution: if N is greater than or equal to the length of the cell, LEFT returns an empty string rather than an error, so a too-large N silently produces blanks instead of flagging a mistake.
Flash Fill: no formula required
If you would rather not write a formula at all, Flash Fill can detect the pattern from an example. In the column next to your data, type the result you want for the first row, then press Ctrl+E (on Mac, the shortcut is also Ctrl+E in current versions, or use Data > Flash Fill from the ribbon). Excel guesses the rule from your example and fills the rest of the column.
For instance, if A1 is 45678 and you type 4567 in B1, pressing Ctrl+E fills the column below with each value minus its last character. Flash Fill is fast for one-off cleanups, but it produces static text, not a live formula, so it does not update if the source data changes. It also needs the original data in an adjacent column to work from. See our walkthrough on how to use Flash Fill in Excel for more patterns.
Text to Columns (fixed width)
When every value ends with the same number of characters you want to discard, Text to Columns can split them off in place. Select your column, go to Data > Text to Columns, choose Fixed width, and click in the ruler to draw a break line just before the characters you want removed. On the final step you can set the unwanted segment’s column format to Do not import column (skip) so only the part you keep lands in the sheet.
This works best for consistent-length data such as ABC-01, ABC-02, where the suffix is always the same width. It is less suited to variable-length values, since the break position is fixed rather than counted from the end of each string. For more, see our guide on how to split one cell into two in Excel.
Digit vs character: why the result is sometimes text
Here is the distinction that causes the most confusion. =LEFT(...) always returns text, even when the original cell held a number. Excel treats the output of any text function as a string. So =LEFT(12345, 4) gives you the text "1234", not the number 1234.
For display and copying, that is often fine. But if you need arithmetic or numeric sorting, text masquerading as a number will misbehave: it left-aligns by default, sorts as text (so "100" sorts before "99"), and may trigger the green “number stored as text” warning. When your goal is to remove the last digit of a number and keep a genuine number, use math instead of text functions.
True numeric removal of the last digit
To drop the final digit of a whole number and keep the result numeric:
=TRUNC(A1/10)Dividing by 10 shifts the number one place to the right, and TRUNC chops off the decimal portion without rounding. If A1 is 12345, A1/10 is 1234.5, and TRUNC returns 1234 as a number you can calculate with. Avoid ROUND here, because =ROUND(12345/10, 0) would round 1234.5 up to 1235 rather than truncating it.
An equivalent approach uses MOD to subtract the last digit before dividing:
=(A1 - MOD(A1, 10)) / 10MOD(A1, 10) returns the last digit (the remainder when dividing by 10), you subtract it so the number ends in zero, then divide by 10. For 12345, that is (12345 - 5) / 10 = 1234. Both formulas give the same result; TRUNC(A1/10) is shorter, while the MOD version is handy when you also want the last digit itself.
Both numeric methods assume a positive whole number. With negative numbers, TRUNC truncates toward zero (so TRUNC(-12345/10) returns -1234), which is usually the intended behavior.
Method comparison
| Method | Formula / action | Returns | Best for |
|---|---|---|---|
| LEFT + LEN | =LEFT(A1, LEN(A1)-1) | Text | Any value, text or number; removing a trailing character |
| Remove last N | =LEFT(A1, LEN(A1)-N) | Text | Stripping a fixed-length suffix |
| TRUNC | =TRUNC(A1/10) | Number | Keeping a true number after dropping the last digit |
| MOD | =(A1-MOD(A1,10))/10 | Number | Numeric result, or when you also need the last digit |
| Flash Fill | Type example, Ctrl+E | Text (static) | Quick one-off cleanup, no formula |
| Text to Columns | Data > Text to Columns > Fixed width | Text or number | Consistent fixed-width suffixes |
Cleaning up stray spaces first
If a cell looks like it has an extra character that LEFT won’t remove cleanly, the culprit is often a trailing space rather than a visible digit. Wrap your reference in the TRIM function to strip extra spaces before counting:
=LEFT(TRIM(A1), LEN(TRIM(A1))-1)TRIM removes leading, trailing, and repeated interior spaces, leaving single spaces between words. For data pasted from the web or other systems, trailing spaces are a frequent reason a character count comes out one higher than expected. Our guide to removing spaces from text in Excel covers stubborn non-breaking spaces that TRIM alone cannot catch.
Troubleshooting
Leading zeros disappeared. If your data is something like ZIP codes or part numbers stored as text (00450), be careful: the numeric methods (TRUNC, MOD) treat the value as a number, which discards leading zeros entirely. Stick with the text method =LEFT(A1, LEN(A1)-1) so the remaining digits keep their zeros. If Excel keeps stripping leading zeros the moment you type them, that is a cell-formatting issue covered in our guide on how to stop Excel from changing numbers (format the cells as Text, or prefix entries with an apostrophe).
The result is text, not a number. As explained above, LEFT always outputs text. If you need a number, either switch to =TRUNC(A1/10), or wrap the LEFT formula in VALUE: =VALUE(LEFT(A1, LEN(A1)-1)). The VALUE function turns a numeric-looking string back into a real number, but it errors on non-numeric content, so only use it when the trimmed result is genuinely a number.
The formula returns a blank or an error. A #VALUE! or empty result usually means N is larger than the cell’s character count, or the cell is empty. Check LEN(A1) on its own to confirm the actual length, including hidden spaces.
Flash Fill filled the wrong pattern. An ambiguous first example can produce surprising guesses. Give it one or two more example rows and press Ctrl+E again, or fall back to the LEFT formula for predictable results.
Frequently Asked Questions
How do I remove the last digit in Excel?
Use =LEFT(A1, LEN(A1)-1) to drop the last character as text, or =TRUNC(A1/10) to remove the last digit of a number and keep a numeric result. Choose the text formula if your value has leading zeros or contains letters, and the TRUNC formula if you need to do math with the answer.
How do I remove the last character in Excel?
The formula =LEFT(A1, LEN(A1)-1) removes the last character of any cell, whether it holds text or a number. The LEFT function returns everything except the final character by using LEN to count the total length and subtracting one.
How do I delete the last number in Excel and keep it a number?
Use =TRUNC(A1/10). Dividing by 10 shifts the value one decimal place and TRUNC removes the decimal without rounding, so 12345 becomes the number 1234. The alternative =(A1-MOD(A1,10))/10 gives the same numeric result.
How do I remove the last N characters in Excel?
Replace the 1 in the LEFT formula with the count you want to drop: =LEFT(A1, LEN(A1)-N). For example, =LEFT(A1, LEN(A1)-4) removes the last four characters. If N is equal to or larger than the cell length, the formula returns an empty string.
Can I remove the last digit without a formula?
Yes. Type the desired result for the first row in an adjacent column and press Ctrl+E to trigger Flash Fill, which detects the pattern and fills the rest. The output is static text rather than a live formula, so it will not update if the source data changes. See our Flash Fill guide for details.
Why does my result lose its leading zeros?
The numeric methods (TRUNC and MOD) treat the entry as a number, which drops leading zeros like the ones in ZIP codes or part numbers. Use the text formula =LEFT(A1, LEN(A1)-1) instead, and format the cells as Text so Excel stops changing your numbers.
