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

Highlight Column

2.  Click Ctrl+ H

Find and Replace

3. Click the space bar on the Find what section and leave the Replace with section as it is

Space

4. Select Replace All

Replace All

This removes all the spaces in between the numbers in Excel.

Results

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

Helper column named Trim

3. In the Trim column, enter the trim formula in the first cell in this column “=TRIM(A2)”

=TRIM() Funtion to remove spaces before numbers

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

drag the formula

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.

copy paste the data

6. Select the helper column and Delete it

Delete the helper column

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,” “,” “)

=substitute() function to remove spaces before numbers

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

Leave a Reply

Discover more from Excel Wizard

Subscribe now to keep reading and get access to the full archive.

Continue reading