Does this situation sound familiar to you: You compare texts in Excel and they should be equal. But instead, Excel says the two cells are not the same. Oftentimes, there is a hidden space in the end of a text cell. Very troublesome: You can’t really see it because the blank character is at the end of the text. In this article we explore 4 methods of how to remove the last character of a text in Excel if it is a space.
Before we start: If you only want to remove trailing space characters and nothing else, this is the right article for you. But if you also want to remove leading space characters or double spaces, please look for the TRIM function (learn more about trimming here).
Let’s take a look at an example: We have two columns with apparently the same data. But a simple comparison indicates, that the first data entry in row 1 is not equal. As we can’t see an obvious difference, the probable reason is a space character at the end of one of the data entries.
In this article we explore 4 ways of how to remove a last space character of a text cell in Excel.
Ad
Contents
Have you ever built an app based on Excel?
Method 1: Remove the last letter if it is blank manually
For most Excel problems, there is a manual way. Also in this case. Instead of creating complex formulas, often removing the last character manually might be the fastest way.
- Select the cell which you expect to have a space in the end.
- Press F2
for entering the cell. - Press Backspace
on the keyboard for deleting the last character. - Press the Enter
key for leaving the cell.
If there are just a few cells with spaces in the end, this method might be fastest. You don’t have to use the mouse so that this sequence of keys to press will become ingrained quickly.
Method 2: Remove the last letter of a cell if it is a space with formulas
If you have many cells with spaces in the end or you are not sure if there is, you should consider using a formula. Therefore, we use the LEN, LEFT and RIGHT formulas.
You need a combination of both LEN, LEFT and RIGHT. Also, you have to use the IF formula. In the picture on the right hand side you can see the complete formula. The following numbers are corresponding to the green numbers on the image.
- IF(RIGHT(B5,1)=” “,: This first part checks whether the last character of cell B5 is a space.
- LEFT(B5,: If the last character is a space, we got to remove it. Therefore, we make Excel to return the left part of the text but without the last character.
- LEN(B5)-1),: We have to determine the length of the text and deduct 1 for the last character.
- B5): If the last character is not a space, we take the original cell content without making any changes.
The complete formula is:
=IF(RIGHT(B5,1)=" ",LEFT(B5,LEN(B5)-1),B5)So if you want to use the formula, the just copy it from above and replace “B5” with you cell reference.
Method 3: Remove the last space character of a cell faster and easier
If you want to use a faster way for removing the last character of a cell – if it is a space – please take a look at the Excel add-in ‘Professor Excel Tools’: Just select all the cells you want to make sure they don’t have a space at the end and press the button ‘Delete Last Letter if Blank’.
Download and try it for free: No sign-up, no installation (just activation within Excel) and a lot of other useful functions.
This function is included in our Excel Add-In ‘Professor Excel Tools’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.
Method 4: Remove all the spaces – except those between words
There is another option, which is not as exact, but in most cases still possible: Use the TRIM function. TRIM removes all the spaces except single spaces between words.
It’s very easy to use: =TRIM(A1) removes all the spaces of cell A1 (as said, single space characters between words stay).
Please note:
- Spaces in the beginning of the cell will also be removed.
- Double or triple spaces (and more, of course) between words will be reduced to one single space.
If that’s fine for you, the TRIM formula is a nice option.
For more information about trimming, please refer to this article.
Ad
This post is really of great use to delete spacing….And I should thank you for This….
However try considering TRIM option also for deleting spacing at end…..which is of great use
Hi Santosh, thanks for the comment. I’ve added it as number four.
Hello, this is giving me fits. There are several non-blank or non-space characters at the end of the numerical data. The Trim() doesn’t shrink the string and the Text to Columns doesn’t seem to allow for special Chars that must be there. I have far to much data to manually go through and click on the end and backspace back to remove them.
I have a number let’s say 73095 with possibly 4 or more “Blank” spots at the end which is preventing me from doing calculations on the data. The normal ‘Convert to Number’ option is not available. I have tried TRIM() and CLEAN(), CONCATENATE() nothing seems to remove the trailing blank spaces short of manually clicking in the address bar for the cell and manually removing them.
Obi Wan – You are our last hope.
I think it’s the Sith, it is simple yet diabolical in nature.
Dear Henrik Schiffner,
Thanks for your post for deletion of space. I have used it as a conditional deleting meaning that if the last letter is “E” OR “F”. Bang, I got the result.
How to make excel faster when it has to deal with 100,000 rows.
If the reply is chargeable I do not want a reply. Otherwise yes.
Auf wiedersehen
Ich danke du
Hi Narasimhan,
no, I don’t charge for my reply 🙂 However, I don’t also reply…
For 100,000 rows it should not be a problem yet. I suppose you use some IF / RIGHT functions? They are usually pretty fast – lookups on the other side start to struggle with 100,000 rows and up.
Best regards,
Henrik