

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.
Example: For no obvious reason, the first data entry is not equal.
Let’s take a look at an example: We got 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.
Contents
Manually: Enter the cell (key F2), delete the last charachter (Backspace) and confirm with Enter.
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.
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.
Remove the last character if it is blank with a formula in Excel
If you got 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.
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.
Just press ‘Delete Last Letter if Blank’ for removing the last character if it is a space in Excel.
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'
Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook can't be wrong.
There is another option, which is not as exact, but in most cases still possible: Use the TRIM formula. 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:
If that’s fine for you, the TRIM formula is a nice option.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Comments 3
Santosh Reddy
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
Henrik Schiffner
Hi Santosh, thanks for the comment. I’ve added it as number four.
Dave
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.