How to Remove Spaces before Numbers in Excel

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.