When importing data from external sources like the web into Excel, you might encounter unwanted leading, trailing, or extra spaces between words and numbers. Such spaces can lead to calculation errors and disrupt data analysis. This guide will introduce several methods to efficiently eliminate these superfluous spaces, ensuring the accuracy and integrity of your data.

Remove leading, trailing, extra spaces between words or numbers
By TRIM function
By a versatile text tool with few clicks
Remove all spaces between numbers or characters
By Find and Replace feature
By SUBSTITUTE function
Remove non-breaking spaces ( )
Remove non-printable characters

Video: Remove Spaces

Remove leading, trailing, extra spaces between words or numbers

Working with text data, such as names, you might often encounter unnecessary spaces. Let's look at how to get rid of these.

Remove leading, trailing, extra spaces between words or numbers by TRIM function

Excel's TRIM function is designed to remove leading, trailing and extra spaces in a text string. Here's how to use it.

Step 1: Select a cell and use TRIM function

If you want to remove the leading, trailing spaces and extra spaces between words in cell A4, in an empty cell, use the TRIM function, then press Enter key.

=TRIM(A4)

Step 2: Apply the formula to other cells and get the results

After getting the first result, drag the fill handle of the formula cell (B4 in this example) down to apply the formula to other cells, here we drag to cell B8.

Remove leading, trailing, extra spaces between words by a versatile text tool with few clicks

Compared to the TRIM function, I highly recommend the Remove Spaces tool from Kutools for Excel. It's a game-changer: no need for complex formulas, offers flexibility in removing various spaces, handles batch selections, and even tackles non-breaking spaces.

Select the cells you want to handle, click Kutools > Text > Remove Spaces, choose one option as you need, and preview the result in the right section, and then click OK. Here I choose All excess spaces option to remove leading, trailing and extra spaces between words.

Remove all spaces between numbers or characters

Removing spaces between numbers (including leading spaces and trailing spaces) is crucial to avoid calculation errors. Fortunately, you can effortlessly achieve this with the following methods:

Remove all spaces by Find and Replace feature

The Find and Replace feature in Excel is a quick, formula-free way to remove all spaces.

Step 1: Select the cells you want to remove all spaces

Step 2: Press Ctrl + H to display Find and Replace dialog

You also can find this feature in the ribbon by clicking Home > Find & Select > Replace.

Step 3: Replace spaces with nothing in the Find and Replace dialog

In the Find and Replace dialog, set as follows:

1. Type a space into the Find what textbox.
2. Leave nothing in the Replace with textbox.
3. Click Replace All.
4. Click OK to finish.
   

Result

Remove all spaces by SUBSTITUTE function

If you want to remove all spaces but preserve the original data, you can use the SUBSTITUTE function to obtain the data without any spaces in a new column.

Step 1: Select a cell and use SUBSTITUTE function

To remove the spaces in cell A4, please use the below formula in any empty cell, then press Enter key to get the first result.

=SUBSTITUTE(A4," ","")

Step 2: Apply the formula to other cells and get the results

After getting the first result, drag the fill handle of the formula cell (B4 in this example) down to apply the formula to other cells.

Remove non-breaking spaces ( )

When importing data from other sources, you might encounter non-breaking spaces such as html character   which is not removed by using TRIM function in Excel, you can use a formula that combine TRIM and SUBSTITUTE functions to remove extra spaces including non-breaking spaces.

Step 1: Select a cell and use formula

To remove extra spaces including non-breaking spaces in cell A4, please use the below formula in any empty cell, then press Enter key to get the first result.

=TRIM(SUBSTITUTE(A4,CHAR(160)," "))

Step 2: Apply the formula to other cells and get the results

After getting the first result, drag the fill handle of the formula cell (B4 in this example) down to apply the formula to other cells.

Remove non-printable characters

Some data imported may contain extra spaces and non-printable characters (ASCII code 0-31). Here's how to remove them:

Step 1: Select a cell and use formula

To remove extra spaces and the non-printable characters in cell A4, please use the below formula in any empty cell, then press Enter key to get the first result.

=TRIM(CLEAN(A4))

Step 2: Apply the formula to other cells and get the results

After getting the first result, drag the fill handle of the formula cell (B4 in this example) down to apply the formula to other cells.

The information provided above details how to remove spaces in Excel. I hope you find it valuable and beneficial. For more invaluable Excel tips and tricks that can transform your data processing, dive in here.