Remove Spaces Between Words and Numbers in Excel

Many a time, when you copy and paste some data directly from an external source such as a web page or a .txt file into an Excel worksheet, it is quite natural that you get some unwanted extra spaces. They could be leading spaces (before) or trailing spaces (between two words or numbers). Working with such a file may not be useful to you as you might not be able to generate a pivot table or an excel chart or use it as a reference to some other cell. The question here arises, how to remove or delete these unwanted extra spaces in Excel? Deleting the extra spaces in Excel is basically known as cleaning up of excel data. There are multiple ways that you can use to remove these additional unwanted spaces in Excel and clean up your data for the required analysis.

In this blog, you would learn three methods that you can use to clean up your data. Before, starting with each of the methods, let us first take one data example

Sample Data

In the below screenshot, the word ‘Excel Unlocked’ has been written in column A. The first one in this list is the perfect one (without any extra spaces). Rest all have extra spaces either at the beginning or between the words.

Sample Data Remove Spaces Excel

The task here is to remove the leading and trailing spaces from the words.

Various Methods to Remove Spaces in Excel

As mentioned in the introduction of this blog, there are multiple ways to delete the spaces in Excel. It includes the formula-based method as well as without using the formula.

  • Using the TRIM function
  • Find and Replace Functionality
  • Flash Fill Feature

At the end of this blog, as a bonus tip and trick, we would also unlock the technique to remove ALL the spaces in Excel.

Let us now start with each of these methods one by one.

Remove Spaces Using TRIM Function

TRIM Function or formula is used to clean the data in Excel. It converts a disordered data into a proper form. This formula has only one attribute which is the ‘text’ or the cell reference of the text.

Enter the following formula in cell B3 to removes the leading and trailing spaces.

=TRIM(A3)

TRIM Function - Remove Spaces

As soon as you press the ‘Enter’ key on your keyboard, you would see that Excel removes the extra spaces between the word ‘Excel’ and ‘Unlocked’ and kept only 1 space character.

Result of TRIM Function

Now, copy the cell B3 using Ctrl+C, and paste (Ctrl+V) it to other cells in column B.

Finally, you would notice that Excel removed the additional spaces at the beginning and also there is only one space character between the two words.

TRIM Formula Copied to Other Cells

Using Find and Replace Functionality

The above method was a formula-based approach to remove unwanted spaces in Excel. Those who do not want to use the formula, they may use the feature called ‘Find and Replace’.

However, unlike the above method, this is a bit lengthy approach.

Follow the procedure below to achieve the same:

Select the cells that contain your data (in my case it is cells A2 to A6).

Then press Ctrl+H to open the ‘Find and Replace’ option on your screen.

Find and Replace - Remove Spaces Excel

Put your cursor in the ‘Find What’ section of this dialog box and press the Space key on your keyboard twice.

Similarly, put the cursor in the ‘Replace WIth’ section and then press the Space key only once and then click on the ‘Replace All’ button.

Find What and Replace With - Remove Spaces Excel

Repeat the above steps multiple times until all the leading and trailing space characters get removed. (This is the reason why this method is generally not preferred over the first method)

Remove Spaces Between Words in Excel

Remove Spaces Using Flash Fill Feature

The ‘Flash Fill’ feature is a new feature available in Excel 2013 and later versions. We have a dedicated blog that contains detailed knowledge about the ‘Flash Fill’ feature. Click on the below link to learn it in detail.

Using Flash Fill Functionality in Excel

Once, you have gone through the above blog, follow the below steps to delete the unwanted spaces.

Enter the word ‘Excel Unlocked’ in cell B2 and press ‘Enter’. Similarly, again write the word ‘Excel Unlocked’ in cell B3.

Now, select the cell B4.

Under the ribbon bar, click on the ‘Data’ tab. There you would find the button ‘Flash Fill’ under the group ‘Data Tools’.

Flash Fill Excel Navigation

Click on the ‘Flash Fill’ button and you would notice the Excel trims and cleans the data.

Refer to the below demonstration.

Flash Fill Demonstration - Remove Spaces Excel

Now, it is time to learn the trick to remove ALL the spaces in a cell in Excel.

Remove ALL Spaces in Excel – A Bonus Trick

Below is the sample dataset wherein there are spaces in between the sales amounts in cell A2. But, this does not represent the correct amount format and you cannot use it the way it is.

In order to remove the spaces, follow the below steps:

Select the cells that contain your data (in my case it is cells A2 to A6).

Then press Ctrl+H to open the ‘Find and Replace’ option on your screen.

In the ‘Find What’ section, press the Space key once on your keyboard. And leave the ‘Replace With’ section as it is. Do not enter anything over there.

Remove All Spaces Excel - Find & Replace

Finally, click on the ‘Replace All’ button and you would notice that excel removes the space characters from the selected cells.

Remove All Spaces in Number - Result

This brings us to the end of this blog. Share your views and comments in the comments section below.

Leave a Comment