Removing the last characters in Excel cells is one of your everyday tasks if you work a lot with text data: a trailing comma, a unit of measure, a code at the end of a product name. Below are all the ways I know, from a simple formula to regular expressions and processing right in place.
If by “cut characters” you mean deleting them for good, read on. If you need to cut them and paste into the next cell, see how to extract characters by their position in a cell. And if you need to remove everything after a certain character rather than a fixed number of characters, see how to remove text up to a certain character.
When characters at the end of cells become extra
Extra characters at the end of cells appear for many reasons:
- Extra spaces: they come with text copied from websites, PDFs or other files and break sorting, lookups and formulas (“ABC” ≠ “ABC ”). For them, there is a separate article on how to remove extra spaces.
- Extra commas or periods: a list was typed by hand, and the last item ends with “,” or “.”.
- Delimiters: a semicolon, a tab or a special character added when exporting data from another program.
- Line breaks, CHAR(10) or CHAR(13): common after importing from CSV or XML or copying from the web.
- Codes and units: “Sofa 200x90 cm”, “SKU-1024-XL”, where the tail has to go.
Removing characters at the end of a cell seems simple, but there are many nuances that can turn it into a real challenge. Whatever your case is, this page will help you untangle it.
Formula to remove the last N characters
To remove the last character in a cell, we use the LEFT and LEN functions:
=LEFT(A1,LEN(A1)-1)
This formula takes the text from cell A1 and cuts one character off the end: LEN returns the length of the text, and LEFT takes all characters except the last one.
To remove several characters at once, for example 2, replace 1 with the number of characters you want to remove:
=LEFT(A1,LEN(A1)-2)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Text | −1 character | −2 characters | −3 characters |
| 2 | apples, | apples=LEFT(A2,LEN(A2)-1) | apple=LEFT(A2,LEN(A2)-2) | appl=LEFT(A2,LEN(A2)-3) |
| 3 | Sofa 200x90 cm | Sofa 200x90 c=LEFT(A3,LEN(A3)-1) | Sofa 200x90 =LEFT(A3,LEN(A3)-2) | Sofa 200x90=LEFT(A3,LEN(A3)-3) |
| 4 | SKU-1024-XL | SKU-1024-X=LEFT(A4,LEN(A4)-1) | SKU-1024-=LEFT(A4,LEN(A4)-2) | SKU-1024=LEFT(A4,LEN(A4)-3) |
LEN counts the characters, LEFT takes all of them except the last N. Change the number in the formula to cut more or fewer.
| Before | =LEFT(A1,LEN(A1)-1) | =LEFT(A1,LEN(A1)-3) |
|---|---|---|
| apples, | apples | appl |
| Sofa 200x90 cm | Sofa 200x90 c | Sofa 200x90 |
| SKU-1024-XL | SKU-1024-X | SKU-1024 |
Remove the last N characters with !SEMTools
A formula always needs a helper column: type it, fill it down, paste the result as values and delete the original. If you don’t need to keep the source data, !SEMTools removes the characters right in place, in the whole column at once:
- Select the range.
- On the !SEMTools tab, go to Delete → Characters → Trim → From End of Cell.
- Enter the number of characters to remove and click OK.
Remove a specific character at the end of a cell (only if it’s there)
For example, a cell ends with a period or another specific character, and you need to remove it only if it’s there. The RIGHT function checks the last character, and IF decides what to return:
=IF(RIGHT(A1,1)=".",LEFT(A1,LEN(A1)-1),A1)
| A | B | |
|---|---|---|
| 1 | Text | Result |
| 2 | Order shipped. | Order shipped=IF(RIGHT(A2,1)=".",LEFT(A2,LEN(A2)-1),A2) |
| 3 | Order shipped | Order shipped=IF(RIGHT(A3,1)=".",LEFT(A3,LEN(A3)-1),A3) |
| 4 | Total: 25. | Total: 25=IF(RIGHT(A4,1)=".",LEFT(A4,LEN(A4)-1),A4) |
Row 3 has no period at the end, so IF returns the text unchanged. The same check with CHAR(10) removes a trailing line break.
The same way, you can remove a trailing line break by replacing "." with CHAR(10):
=IF(RIGHT(A1,1)=CHAR(10),LEFT(A1,LEN(A1)-1),A1)
Replace word endings by condition
Sometimes you need to bring words to one form, for example turn plurals ending in “-ies” into the singular: “categories” → “category”, “companies” → “company”.
=IF(RIGHT(A1,3)="ies",LEFT(A1,LEN(A1)-3)&"y",A1)
| A | B | |
|---|---|---|
| 1 | Text | Result |
| 2 | categories | category=IF(RIGHT(A2,3)="ies",LEFT(A2,LEN(A2)-3)&"y",A2) |
| 3 | companies | company=IF(RIGHT(A3,3)="ies",LEFT(A3,LEN(A3)-3)&"y",A3) |
| 4 | series | sery=IF(RIGHT(A4,3)="ies",LEFT(A4,LEN(A4)-3)&"y",A4) |
| 5 | products | products=IF(RIGHT(A5,3)="ies",LEFT(A5,LEN(A5)-3)&"y",A5) |
Note “series” → “sery”: a mechanical rule doesn’t know exceptions, so check the results or keep a list of exceptions.
The formula checks the ending of the word and, if it matches, replaces it with “y”.
In computational linguistics, cutting off endings is called stemming, and bringing words to their base form is called lemmatization. By the way, !SEMTools has tools for this too. See the article on lemmatization of text in Excel.
Remove the last occurrence of a character
A rare and tricky task is to remove exactly the last occurrence of a character, for example the last comma. The solution counts the occurrences and uses the fourth argument of the SUBSTITUTE function:
=SUBSTITUTE(A1,","," and",LEN(A1)-LEN(SUBSTITUTE(A1,",","")))
| A | B | |
|---|---|---|
| 1 | Text | Result |
| 2 | red, green, blue | red, green and blue=SUBSTITUTE(A2,","," and",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))) |
| 3 | Moscow, Paris | Moscow and Paris=SUBSTITUTE(A3,","," and",LEN(A3)-LEN(SUBSTITUTE(A3,",",""))) |
| 4 | one | #VALUE!=SUBSTITUTE(A4,","," and",LEN(A4)-LEN(SUBSTITUTE(A4,",",""))) |
LEN(A1)-LEN(SUBSTITUTE(A1,",","")) is the number of commas, and it becomes the instance number for SUBSTITUTE. Without commas the instance number is 0, and SUBSTITUTE returns #VALUE! — wrap the formula in IFERROR(…,A1) if that can happen.
The formula counts the commas in the cell and replaces only the last one with “ and”: “red, green, blue” becomes “red, green and blue”. If a cell has no commas at all, SUBSTITUTE gets 0 as the occurrence number and returns #VALUE!, so for mixed data wrap the formula in IFERROR(…,A1). In this case, it’s not about the literally last character in the cell, but about the last occurrence of a certain character.
Remove the last characters with REGEXREPLACE
A very flexible way to remove characters is regular expressions (regex for short). Among popular spreadsheet apps, REGEXREPLACE first appeared in Google Sheets, then in the !SEMTools add-in, and now it’s also available in recent Excel 365 updates.
In !SEMTools, the regex functions are available in the free version with no time limit. So if your Excel doesn’t support regex, the add-in can be very useful :)
The key character of all patterns for removing characters at the end of a cell is “$”, the end of the string, which in our case means the end of the cell content. Regex lets you avoid bulky formulas that only true Excel gurus can reproduce from memory, and the basic syntax is not that complicated, as the examples below show.
Remove the last character
The pattern “.$” means “any character at the end of the string”:
=REGEXREPLACE(A1,".$","")
| A | B | |
|---|---|---|
| 1 | Text | Result |
| 2 | apples, | apples=REGEXREPLACE(A2,".$","") |
| 3 | Report 2026; | Report 2026=REGEXREPLACE(A3,".$","") |
. is any character, $ is the end of the cell. In Excel 365 this is the built-in REGEXREPLACE; in older versions the function with the same name comes with !SEMTools.
Remove the last N characters
To remove several characters at the end, for example 3, use the pattern “.{3}$”:
=REGEXREPLACE(A1,".{3}$","")
| A | B | |
|---|---|---|
| 1 | Text | Result |
| 2 | SKU-1024-XLL | SKU-1024-=REGEXREPLACE(A2,".{3}$","") |
| 3 | Model 2026 | Model 2=REGEXREPLACE(A3,".{3}$","") |
{3} repeats the previous token exactly three times. If the cell is shorter than 3 characters, nothing is removed — no error, unlike LEFT + LEN.
Remove all digits at the end of a cell
If the digits at the end are the only digits in the cell, there are simpler ways: see how to remove numbers from cells. Here we are talking about cases where the beginning and the middle of the text may contain other digits that must stay.
=REGEXREPLACE(A1,"\d+$","")
To remove the last two numbers and any characters between them, for example a size like “200x90”, use this pattern:
=REGEXREPLACE(A1,"\d+\D+\d+$","")
| A | B | C | |
|---|---|---|---|
| 1 | Text | Digits at the end | Size at the end |
| 2 | Sofa 200x90 | Sofa 200x=REGEXREPLACE(A2,"\d+$","") | Sofa =REGEXREPLACE(A2,"\d+\D+\d+$","") |
| 3 | Cable USB 2m 150 | Cable USB 2m =REGEXREPLACE(A3,"\d+$","") | Cable USB =REGEXREPLACE(A3,"\d+\D+\d+$","") |
| 4 | iPhone 15 | iPhone =REGEXREPLACE(A4,"\d+$","") | iPhone 15=REGEXREPLACE(A4,"\d+\D+\d+$","") |
\d+$ removes only the last group of digits; \d+\D+\d+$ removes two numbers and everything between them, like “200x90”. In “Cable USB 2m 150” the second pattern removes “2m 150”, because “m ” are non-digits between two numbers.
For removal by regex without formulas, there is a procedure in the Delete characters menu on the !SEMTools tab. Select the cells, enter the pattern, and the tails disappear right in place. You may also wonder whether you can cut these values and paste them next to the cells. Yes, the “Extract by regex” procedure does exactly that, so one pattern splits a column into two: the cut value and what remains.
Remove all letters at the end of a cell
=REGEXREPLACE(A1,"[A-Za-z]+$","")
To remove exactly n letters, specify the number in curly braces, for example 3:
=REGEXREPLACE(A1,"[A-Za-z]{3}$","")
| A | B | C | |
|---|---|---|---|
| 1 | Text | All letters at the end | Exactly 3 letters |
| 2 | SKU-1024XL | SKU-1024=REGEXREPLACE(A2,"[A-Za-z]+$","") | SKU-1024XL=REGEXREPLACE(A2,"[A-Za-z]3$","") |
| 3 | Code 55ABCD | Code 55=REGEXREPLACE(A3,"[A-Za-z]+$","") | Code 55A=REGEXREPLACE(A3,"[A-Za-z]3$","") |
| 4 | Order 15 | Order 15=REGEXREPLACE(A4,"[A-Za-z]+$","") | Order 15=REGEXREPLACE(A4,"[A-Za-z]3$","") |
[A-Za-z]+$ removes all letters at the end; {3} removes exactly three. In “Order 15” there are no letters at the end, so nothing changes.
Remove n digits at the end of a cell
For example, a 4-digit year at the end:
=REGEXREPLACE(A1,"\d{4}$","")
| A | B | |
|---|---|---|
| 1 | Text | Result |
| 2 | Sales report 2026 | Sales report =REGEXREPLACE(A2,"\d{4}$","") |
| 3 | Q3 25 | Q3 25=REGEXREPLACE(A3,"\d{4}$","") |
\d{4}$ requires exactly four digits at the end: in “Q3 25” there are only two, so the text stays as is.
Remove everything after a certain character at the end
Say a string may end with “#”, and sometimes extra characters stick to it. They need to go, but “#” itself must stay:
=REGEXREPLACE(A1,"#.+$","#")
| A | B | |
|---|---|---|
| 1 | Text | Result |
| 2 | Item #42abc | Item #=REGEXREPLACE(A2,"#.+$","#") |
| 3 | Note#x | Note#=REGEXREPLACE(A3,"#.+$","#") |
| 4 | Plain text | Plain text=REGEXREPLACE(A4,"#.+$","#") |
#.+$ finds “#” and everything after it to the end, and the replacement puts “#” back. Cells without “#” don’t change.
Why regex is worth it
- Short formulas without nested functions.
- Flexibility: several conditions in one pattern.
- Compatibility: the same syntax works in Google Sheets, !SEMTools and Excel 365.
Why the last characters aren’t removed: common mistakes
- The formula returns #VALUE!. The cell is shorter than the number of characters to remove, or it’s empty, so LEFT gets a negative number. Wrap the formula: =IFERROR(LEFT(A1,LEN(A1)-3),"").
- The wrong character was removed. The cell ends with an invisible space or line break, and the formula removed it instead of the character you see. Check the length with LEN or remove extra spaces first.
- Numbers and dates turned into strange values. LEFT returns text, and a date is a number for Excel, so the formula cuts its serial number. Convert dates to text first with the TEXT function.
See also:
Remove the last characters, endings and trailing junk from whole columns right in place, without formulas or helper columns.
!SEMTools solves this and hundreds of other text tasks in a couple of clicks!
Related operations with characters
I hope you found what you were looking for on this page and were able to remove the last characters in your cells. LEFT + LEN works in any version, regex covers complex tails in one pattern, and for whole columns !SEMTools does it in place in a couple of clicks, with no syntax to remember.
You may also be interested in related operations:
- Remove the first N characters in Excel cells
- Extract the last N characters of a cell
- Get the first N characters of a cell
- Remove all characters except digits
- Remove all characters except letters and numbers
- Remove the first word or the first N words in cells
- Remove the last word or the last N words in cells