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

space, remove, blank, cell, excel

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).

example, not, equal, text, excel, last, character, blank, space
Example: For no obvious reason, the first data entry is not equal.

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

delete, last, character, manually, space, excel, blank
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 computer_key_F2 for entering the cell.
  3. Press Backspace computer_key_Backspace on the keyboard for deleting the last character.
  4. Press the Enter computer_key_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

blank, space, formula, remove, character, letter, cell, text
Remove the last character if it is blank with a formula in Excel

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 LENLEFT 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

remove, last, character, letter, len, delete, blank, space
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.


Professor Excel Tools Box

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.

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

5 comments

  1. 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

  2. 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.

  3. 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

    1. 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

Leave a comment

Your email address will not be published. Required fields are marked *