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.