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:
- Select the cell or range of cells from which you want to remove white spaces.
- Use the shortcut Control + H to activate the find and replace option.
- In the “find what” input bar, insert a blank space using the space bar, and “Replace With” should remain blank.
- 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 FunctionOnce 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.
I like the find and replace option the best. email me stuff I need to know in excel.
OMG, You make it so simple. This absolutely fixed my problem, thank you so much.
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
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
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.
Thanks a lot Brother, great thing shared,
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.
You did a good job by teaching us excel. Thanks Punnet.
You’re welcome.