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.

Method 1: Remove the last letter if it is blank manually

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.

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

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.  

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:

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

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.

---