There are different ways in which you can remove spaces before numbers in Excel. These are the trim formula, Excel Find and Replace, or special Excel add-in. Extra space in Excel can be found when you paste foreign data into Excel. The leading and trailing spaces can be in between the words and spaces thus making your work look disorganized and disorderly. Apart from that, extra spaces make it difficult for you to perform any mathematical operations with numbers in the cells. Therefore, you need to get rid of them.
Method 1: How to remove all spaces between numbers
Excel identifies all spaces between numbers and removes them. This method is the easiest way to remove excess spaces. Use the steps below;
1. Click “Ctrl+ Space” then highlight all the cells in the column
2. Click Ctrl+ H
3. Click the space bar on the Find what section and leave the Replace with section as it is
4. Select Replace All
This removes all the spaces in between the numbers in Excel.
Method 2: How to remove leading or trailing spaces
In the event that you have irrelevant blanks before and after words in Excel, you can use this method.
The Trim formula
This formula removes extra spaces. Follow the easy steps below;
1. Add an extra help column just at the end of the data you want to remove extra spaces from
2. Name this column Trim
3. In the Trim column, enter the trim formula in the first cell in this column “=TRIM(A2)”
4. Use the Copy and Paste options to copy the same formula across the other cells in the same column. Also, you can drag the formula down
5. Highlight all the cells in the helper column. Press keyboard options Ctrl+ C and copy the cleaned data. Press Ctrl+ V and paste the cleaned data on the original row data.
6. Select the helper column and Delete it
This deletes all extra trailing spaces from the data.
Method 3: How to remove all the spaces in Excel using the =SUBTITUTE() function
The formula chain allows you to delete all the blank spaces between numbers.
1. Create a helper column.
2. In the first cell of the helper column, type the formula =SUBSTITUTE(A2,” “,” “)
3. Use the Copy Paste options to copy the formula to the rest of the cells in the helper column.
4. Select all cells in the original column and copy-paste the cleaned data to this column. Select the helper column and delete it.
Method 4: Using Ablebits Addin
1. Install Abblebits addin free
2. Highlight the rows where you want to delete leading spaces
3. Click on Trim Spaces
4. Choose trim leading spaces
5. Click trim and all the spaces before the numbers will be deleted
VBA Code to remove spaces before numbers in Excel
1. Press Alt + F11 to open the VBA editor in Excel.
2. Insert a new module by right-clicking on any item in the Project Explorer, selecting “Insert,” and then choosing “Module.”
3. Copy and paste the code into the module.
Sub RemoveSpacesBeforeNumbers()
Dim cell As Range
Dim selectedRange As Range
' Get the selected range
On Error Resume Next
Set selectedRange = Selection
On Error GoTo 0
' Check if a range is selected
If Not selectedRange Is Nothing Then
' Loop through each cell in the selected range
For Each cell In selectedRange
' Check if the cell is not empty
If Not IsEmpty(cell.Value) Then
' Remove spaces before numbers
cell.Value = RemoveSpacesBeforeNumbersInString(cell.Value)
End If
Next cell
Else
MsgBox "Please select a range of cells first.", vbExclamation
End If
End Sub
Function RemoveSpacesBeforeNumbersInString(inputString As String) As String
Dim resultString As String
Dim i As Integer
' Loop through each character in the input string
For i = 1 To Len(inputString)
' Check if the current character is a space
If Mid(inputString, i, 1) <> " " Then
' Add the character to the result string
resultString = resultString & Mid(inputString, i, 1)
Else
' Check if the next character is numeric
If i < Len(inputString) Then
If IsNumeric(Mid(inputString, i + 1, 1)) Then
' Skip the space
Else
' Add the space to the result string
resultString = resultString & Mid(inputString, i, 1)
End If
Else
' Add the space to the result string (at the end of the string)
resultString = resultString & Mid(inputString, i, 1)
End If
End If
Next i
' Return the result string
RemoveSpacesBeforeNumbersInString = resultString
End Function
4. Close the VBA editor.
5. Select the range of cells in which you want to remove spaces between numbers.
6.Press Alt + F8 to open the “Macro” dialog.
7. Select “RemoveSpacesBeforeNumbers()” and click “Run.