How to Remove Extra Spaces in Excel

Last Updated: December 02, 2023
puneet-gogia-excel-champs

- Written by Puneet

Clean data is our birthright, but sometimes we are not able to access it.

Sometimes, we are not able to use our data the way we want. And one of the biggest reasons which make our data unable to use is…

So if you want to use it we need to remove them all. When you think about this, most of the time when you get data from ERP systems or you have some formulas which not written properly, you get those unwanted white spaces.

So, every time you work on your data you need to verify it first.

And in today’s post, I’m going to show you the exact 4 methods to remove all those unwanted and extra spaces.

Make sure to DOWNLOAD this SAMPLE FILE from here to follow along. Now let’s get started.

1. Remove Extra Spaces using TRIM Function [Trailing and Leading]

It happens sometimes that you get trailing and leading spaces in your data and it looks very nasty.

But we have a very simple and easy method to pull out this garbage and you need only one function to deal with it. 

That’s TRIM.

It removes all unwanted spaces from a text string except the spaces between two words, look at the below example.

remove extra spaces from a cell in excel using trim function

As you can see, we have used TRIM to remove all the unwanted trailing and leading spaces from the cell.

There are 70 characters in the cell and when you have used TRIM, the characters count is decreased to 41, which means it has deleted the 29 spaces from the cell.

2. Combine Clean and Trim to Remove Extra Space from Line Break

There is also another possibility that you’ll get a line break in a text string. In this situation, you need to use combine CLEAN and TRIM where the CLEAN function can remove all non-printing characters which include a line break as well.

remove extra spaces from a cell in excel using clean function

How it works

First of all, CLEAN removes all non-printing characters from the text string, and then TRIM removes all the unwanted spaces from the text string other than the space between two words.

3. Using Clean, Trim, and Substitute to Remove All the Spaces from a Cell

Here we have another type of issue with these white spaces.

It happens sometimes that we want to remove all types of spaces from a cell. But cleaning and trimming are not able to remove all types of spaces.

For example, if you have a non-breaking space in a cell, you will not able to deal with it using both of these functions. And for this problem, you need to combine CLEAN and TRIM with SUBSTITUTE.

Let me show you an example. In the below example, we have a list of mobile numbers in a worksheet and these numbers are surrounded by some unwanted white spaces.

We only need a 10-digit mobile number from it.

remove extra spaces from a cell in excel using trim clean substitute

How it Works

SUBSTITUTE helps you to find a specific character or a set of characters from a text string and replace it with some other characters or a single character.

4. Use the Find and Replace Option to Remove All the Spaces

I have another option to solve the above-mentioned problem and you don’t have to use a formula for that. You can use FIND and REPLACE to replace white spaces from a cell, here are the steps:

remove extra spaces from a cell in excel using find replace option
  1. Select the cell or range of cells from which you want to remove white spaces.
  2. Use the shortcut key: Control + H to activate the find and replace option.
  3. In the “find what” input bar, insert a blank space using the space bar, and “Replace With” should remain blank.
  4. Click on the replace all button, you’ll receive a pop-up message about how many blank spaces are replaced.

Alert: Use this method only when you are confirmed to remove all types of spaces from selected cells.

Get the Excel File

Conclusion

Extra spaces are always irritating. But now you have 4 different methods to remove them and you can choose any of these methods which you think is perfect for you.

I hope this will help you to play well with your data and now you need to tell me one thing.

Do you know another way to deal with this problem?

Please share with me in the comment box, I’d love to hear from you and don’t forget to share this tip with your friends.

9 thoughts on “How to Remove Extra Spaces in Excel”

  1. Hi Puneet,

    Hope you are well.

    I am a Project control Analyst in UK and I have just started visiting your website and have gone through the wildcard/vlookup lesson and now the space removing lesson.

    I just want to say these are awesome lessons – so easy to understand and explained with examples.

    Please keep making such great lessons as excel is a core in Finance.

    Many thank you
    Amana

    Reply
  2. I have two other ways to remove non-printable characters, both use VBA:

    1) This goes cell by cell. So, it is best to select a specific range.
    Sub DoTrim()
    Dim LastRow As Long
    Dim cell As Range, DataRange As Range
    Dim Str As String

    LastRow = Cells(Rows.Count, “G”).End(xlUp).Row
    Set DataRange = Range(“A1:G” &LastRow)

    ‘Delete non-printable “control” characters: _
    ‘ 10=New Line, 12=Form Feed or Page Break, 13=Carriage Return, 32=Space & 160=Nonbreaking Space
    For Each cell In Selection.Cells
    If cell.HasFormula = False Then
    Str = Left(cell.Value, 1)
    While Str = ” ” Or Str = Chr(10) Or Str = Chr(12) Or Str = Chr(13) Or Str = Chr(32) Or Str = Chr(160)
    cell.Value = Right(cell.Value, Len(cell.Value) – 1)
    Str = Left(cell.Value, 1)
    Wend
    Str = Right(cell.Value, 1) ‘New Line
    While Str = ” ” Or Str = Chr(10) Or Str = Chr(12) Or Str = Chr(13) Or Str = Chr(32) Or Str = Chr(160)
    cell.Value = Left(cell.Value, Len(cell.Value) – 1)
    Str = Right(cell.Value, 1)
    Wend
    End If
    Next cell
    End Sub

    2) This using Find/Replace – this is faster
    Sub DoTrim()
    Dim LastRow As Long
    Dim DataRange As Range
    LastRow = Cells(Rows.Count, “I”).End(xlUp).Row
    Set DataRange = Range(“A1:I” & LastRow)
    ‘Range(“A1″, ActiveCell.SpecialCells(xlLastCell)).Select
    DataRange.Select
    With Selection
    ‘CHR(10) is for a New Line
    Selection.Replace What:=”” & Chr(10) & “”, Replacement:=””, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    ‘CHR(12) is for a Form Feed a.k.a. Page Break
    Selection.Replace What:=”” & Chr(12) & “”, Replacement:=””, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    ‘CHR(13) is for a Carriage Return
    Selection.Replace What:=”” & Chr(13) & “”, Replacement:=””, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    ‘CHR(32) is for a Space
    Selection.Replace What:=”” & Chr(32) & “”, Replacement:=””, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    ‘CHR(160) is for a Nonbreaking Space
    Selection.Replace What:=”” & Chr(160) & “”, Replacement:=””, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Cells.Replace What:=” “, Replacement:=””, LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    End With
    End Sub

    Reply
  3. This is a way to remove unwanted extra spaces between words, like in
    ‘this example’
    It will in three steps reduce spaces between words to only one.
    1. Search and replace ‘ ‘ by ‘ |’
    2. Search and replace ‘| ‘ by ”
    3. Search and replace ‘ |’ by ‘ ‘
    In tekst: Search and replace space by space pipe, then pipe space by nothing, then space pipe by space

    Even when there are 100 spaces between the words, these three steps will reduce them to one.

    Essential is that the pipe symbol is NOT used in the file. If you are uncertain about that, search for it before you start replacing or use a string you are most certain of that it does not exist in your file, like ‘xqzxqzxqz’ instead of the pipe symbol.

    Reply
  4. One more method is there to remove spaces.
    ALT +D+E+W+N+F

    Also note when it comes to N i.e next tab make sure that there is no split line or arrows and then click/press finish.

    Reply

Leave a Comment