Space Character: Remove Last Letter in Excel If It Is Blank

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.

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.

1. Select the cell which you expect to have a space in the end.
2. Press F2  for entering the cell.
3. Press Backspace  on the keyboard for deleting the last character.
4. 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.

1. IF(RIGHT(B5,1)=” “,: This first part checks whether the last character of cell B5 is a space. 
2. 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.
3. LEN(B5)-1),: We have to determine the length of the text and deduct 1 for the last character.
4. 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.

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.