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
