Remove Extra Spaces in Excel (Leading and Trailing)

puneet-gogia-excel-champs

- Written by Puneet

Quick Solution for Extra Spaces

  • TRIM – Removes all extra spaces from a text string except for single spaces between words. It is the quickest of all the methods we know for removing extra spaces.
  • CLEAN – Removes all non-printable characters from a text string. You can use it in combination with TRIM.
  • SUBSTITUTE – Replaces all instances of a specific character with another character. Helpful in handling non-breaking spaces.
  • REPLACE – Replaces characters within a text string at a specified position. It is not directly used for spaces but can be combined with other functions.
  • FIND & REPLACE – If you want to remove spaces for a one-time, you can use the find and replace option in Excel.
  • VBA CODE – Write a VBA code to remove all the extra spaces in a single click. You can also create a custom function that can remove trailing and leading spaces from a cell.

Let’s say you’re working on a sales report and notice extra spaces before or after the values, which messes up your calculations. For example, one cell shows ” 5000″ instead of “5000”. It can cause errors in your totals and averages because Excel reads those spaces as part of the text.

To fix this, you can use the TRIM formula. It’s simple: just type =TRIM(A1) in a new cell to remove those extra spaces from the value in cell A1.

But apart from this, you can use other methods as well. In the tutorial, we will learn all the formulas we can use to get rid of these extra spaces (leading and trailing spaces) from a cell value.

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

Remove Extra Spaces using the TRIM Function [Trailing and Leading]

Sometimes, your data has trailing and leading spaces, which look nasty. But we have a very simple method for removing this garbage; you only need one function to deal with it. That’s the TRIM function.

If you have a list of addresses in Excel with extra spaces at the beginning or end, the TRIM function is your best friend. These extra spaces can cause problems when you try to sort or use the addresses. To clean them up, use the TRIM function.

If your address is in cell A1, you can type =TRIM(A1) in a new cell. This formula will remove all the excess spaces from the beginning and end of the address, leaving it clean.

It removes all unwanted spaces from a text string except the spaces between two words. Look at the example. As you can see, we have used TRIM to remove all the unwanted trailing and leading spaces from the cell.

The cell has 70 characters; when you use TRIM, the number of characters decreases to 41, meaning it has deleted the 29 spaces from the cell.

Removing Extra Spaces in an Entire Column

Suppose you have a whole column of data with extra spaces and want to clean it up using the TRIM. You apply the formula to the column. You can use and drag it down to apply to the entire column.

If you have Office 365, Excel 2019, or Excel 2021, you can use =TRIM(A:A), which will dynamically apply to the whole column. The TRIM function will dynamically clean up all the extra spaces for the entire column.

Using methods other than TRIM

TRIM is designed to remove extra spaces from text, leaving only single spaces between words. However, TRIM might not work as expected in some cases, such as when dealing with non-breaking spaces or other invisible characters. TRIM removes only standard spaces (ASCII code 32). TRIM does not remove non-breaking spaces (ASCII code 160). These are often used in web pages and can be inadvertently copied into Excel. And if the spaces at the end of the text are non-breaking spaces, TRIM will not remove them.

Combine Clean and Trim to Remove Extra Spaces

There is also another possibility of a line break in a text string. In this situation, you can combine CLEAN and TRIM, where the CLEAN function can remove all non-printing characters and non-printable spaces, including a line break.

=CLEAN(A1)

How it Works

First, 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.

What are Non-Printable Spaces

Non-printable spaces are characters you can’t see or print but can cause issues with your data. These might come from copying text from websites or other software. These characters can mess up your formulas and sorting.

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

Here, we have another issue with these white spaces. Sometimes, we want to remove all types of spaces from a cell, but CLEAN and TRIM cannot do so.

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

Let me show you an example. In the example below, 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.

How it Works

SUBSTITUTE helps you find a specific character or a set of characters from a text string and replace them with others or a single character.

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 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. When you click the replace all button, a pop-up message will appear telling you how many blank spaces have been replaced.

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

VBA Code to Extra Remove Spaces

Below is a VBA code that removes extra spaces from a worksheet’s selected cells. This code will remove leading, trailing, and multiple spaces between words, leaving only single spaces between words.

To use this code, first, press the Alt + F11 to open the VBA editor. Then, go to Insert > Module to create a new module. In the end, paste the following VBA code into the module.

Sub RemoveExtraSpaces()
Dim cell As Range
Dim selectedRange As Range

' Check if any cells are selected
If TypeName(Selection) <> "Range" Then
MsgBox "Please select the cells where you want to remove extra spaces."
Exit Sub
End If

' Set the selected range
Set selectedRange = Selection

' Loop through each cell in the selected range
For Each cell In selectedRange
' Check if the cell contains text
If Not IsEmpty(cell.Value) And IsText(cell.Value) Then
' Remove extra spaces using TRIM function
cell.Value = Application.Trim(cell.Value)
End If
Next cell

MsgBox "Extra spaces have been removed from the selected cells.", vbInformation
End Sub

Function IsText(cellValue As Variant) As Boolean
IsText = VarType(cellValue) = vbString
End Function

You can also write code to create a custom function in Excel to remove leading and trailing space.

Function RemoveExtraSpaces(text As String) As String
    Dim i As Integer
    Dim cleanText As String
    Dim previousChar As String
    
    ' Initialize variables
    cleanText = ""
    previousChar = ""
    
    ' Loop through each character in the input text
    For i = 1 To Len(text)
        Dim currentChar As String
        currentChar = Mid(text, i, 1)
        
        ' Add character to cleanText if it's not a space, or if it's a space and the previous character is not a space
        If currentChar <> " " Or (currentChar = " " And previousChar <> " ") Then
            cleanText = cleanText & currentChar
        End If
        
        ' Update previousChar
        previousChar = currentChar
    Next i
    
    ' Remove any leading or trailing spaces
    RemoveExtraSpaces = Trim(cleanText)
End Function

Once you paste the code into the VBA editor, go to any cell and use this custom function like a built-in Excel function. For example, if you want to remove extra spaces from the text in cell A1, you would enter:

=RemoveExtraSpaces(A1)

Conclusion

Extra spaces are always irritating. But now you have four different methods to remove them, and you can choose any that is perfect. I hope this will help you work well with your data. 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. Also, don’t forget to share this tip with your friends.

Last Updated: December 02, 2023

9 thoughts on “Remove Extra Spaces in Excel (Leading and Trailing)”

  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