Today, you’ll learn how to get rid of extra spaces in Microsoft Excel. This is a valuable skill to acquire. Stick to this guide and read until the end.

Extra spaces in Excel can ruin more than just the look of your spreadsheet. They make your data harder to read, harder to scan, and much more difficult to work with.

I’ve seen entire reports go wrong just because of a few invisible characters. If you’ve ever attempted to fix this manually, you’re aware of how time-consuming and frustrating it can be. Through this Excel tutorial, I’ll walk you through the easiest ways to remove all those extra spaces.

Using the Excel Flash Fill Tool

Using the Flash Fill tool to remove extra spaces is a neat trick that works almost like Excel is reading your mind. What makes this method great is how fast and hands-on it feels, especially if you’re in the middle of cleaning data and don’t want to waste time creating templates or writing formulas. However, the tool is only available in Excel 2016 and newer editions.

Now, let us find below how to make use of this method in real-world datasets:

Click into a blank column right next to the data with extra spaces and type the cleaned-up version of the first entry exactly how you want it to appear, making sure all extra spaces are removed manually. Press Enter to confirm the entry.

In the next cell of the same column, start typing the cleaned-up version of the second entry, and Excel will automatically suggest a pattern using Flash Fill as light gray text.

If you see the suggestion, press Enter to accept it instantly.

If Flash Fill doesn’t appear automatically, type the second entry, removing extra spaces.

Select both of the cells that you’ve manually populated.

Then go to the Data tab, click Flash Fill, or press Ctrl + E. Excel will instantly fill the rest of the column based on the pattern you provided.

Check the filled values carefully to ensure Excel followed the pattern correctly, as Flash Fill works on prediction and can sometimes misinterpret unusual spacing.

If something looks off, use the Excel undo feature by pressing Ctrl + Z together and adjust the first few entries before trying again.

Using the Find and Replace Tool

The Find and Replace tool is yet another easy-to-use feature of Excel that allows you to remove extra spaces from the selected dataset visually. You basically tell Excel what to look for and what to replace it with, and it obediently cleans up your data.

Find below the effortless steps to use this method:

Select the range of cells where you want to remove the extra spaces by clicking and dragging over them, making sure you include all the rows and columns you want to clean.

Once the source data range is selected, press Ctrl + H to open the Find and Replace dialog box.

In the Find what field, type two spaces by pressing the spacebar twice, because you’re asking Excel to find double spaces.

Leave the Replace with field as a single space by pressing the spacebar once.

Click Replace All, and Excel will instantly replace every double space with a single space across your selected range.

The tool will replace extra spaces with the appropriate number of spaces and will also show a confirmation dialog box.

Finally, review a few cleaned entries visually to make sure the text or numbers look exactly the way you intended.

You can always press Ctrl + Z to undo and repeat the process carefully if there are any errors or the tool omits important characters from the dataset.

Using the TRIM Function

Using the TRIM function to remove extra spaces in Excel is one of those reliable, formula-based methods that always delivers clean results. It automatically removes all unnecessary spaces, keeping only single spaces between words, which is ideal for text entries, names, or descriptions.

Click into a blank cell next to the first entry you want to clean, making sure it aligns with the row you’re working on.

Enter the following formula into the cell:

=TRIM(A2)

Ensure you adjust the cell reference in the above formula to match the source data in your own worksheet.

Hit Enter to calculate the cell.

Hover over the bottom-right corner of the cell until you see a small plus sign, then drag it down to copy the formula for all other rows.

Excel will instantly clean every entry in the same column using the same logic.

Once all rows are cleaned, copy the entire column with the TRIM results by pressing the Ctrl + C keys.

Right-click on the original data column and choose Paste Values.

This replaces the old data with the cleaned version, after which you can delete the helper column if you want.

Congratulations! You’ve successfully removed extra spaces from your data using the TRIM function.

CLEAN + TRIM Function

Mostly, you find extra spaces in datasets imported from different systems or from the web. In such datasets, you also find hidden, non-printable characters. These hidden characters are undesirable as well.

In this situation, you can use the CLEAN and TRIM functions in combination to remove unwanted characters as well as extra spaces.

Select a blank cell to the right of the first entry you want to fix. It must align with the same row as your original data.

Copy and paste the following formula into that cell:

=CLEAN(TRIM(A2))

Don’t forget to get rid of the existing cell references and replace them using cell addresses from your own dataset in Excel.

Hit Enter to remove extra spaces as well as hidden characters.

Click on the fill handle in the bottom-right corner and drag it down until data exists in the source column to the left of the source data.

Excel will clean unprintable and hidden characters and remove extra spaces from every selected row using the same formula.

Using the Text to Columns Tool

Using the Text to Columns tool to remove extra spaces in Excel is a clever yet simple way to clean up your data without writing a single formula. Originally designed to split text into separate columns, this tool has an option that trims extra spaces automatically while keeping the original structure intact.

Here are the simple steps you must follow:

Select the column that contains the text with extra spaces by clicking on its header or dragging over the specific range you want to clean. Ensure you select only the data you need to change because this tool works directly on the original cells.

Go to the Data tab on the ribbon and click Text to Columns to open the wizard.

In the first step, choose Delimited and click Next to move forward.

In the next step, uncheck all delimiter options and only check Space, because you’re asking Excel to split based on spaces.

Also, check the box that says Treat consecutive delimiters as one to remove extra spaces effectively, then click Next.

Finally, in the third step, click Finish.

The Text to Columns tool will split the space-separated text into multiple columns.

When there are redundant leading spaces in text strings, the final columns with text strings will move ahead by one column.

For example, look at the dataset shown above. Rows 2 through 5 had leading spaces, so the resulting columns moved ahead by one column. Instead of columns A and B, I got the results in columns B and C.

However, rows 6 through 8 are still in columns A and B since these had no leading spaces.

Now, to join the texts, select the destination cell and enter the following formula into it:

=TEXTJOIN(" ",TRUE,A2,B2,C2)

Ensure you change the cell references in the above formula according to your own worksheet. The thumb rule is, the formula should include all three columns where the Text to Columns function generated texts.

Hit Enter to join the texts, like combining first and last names to full names.

Now, select this first cell and drag the fill handle down to replicate the formula into the rest of the cells down the column.

That’s it! You’ve successfully removed spaces and combined texts into a complete sentence or data.

Using Power Query

Using Power Query to remove extra spaces in Excel is a smart, structured approach that works great for cleaning up data professionally. Power Query lets you transform your data by applying built-in data cleaning steps, including trimming unnecessary spaces automatically.

Follow along with me as I explain and show you the simple steps:

For an external dataset, go to the Data tab in Excel, click Get Data, hover over From Azure, and then select From Azure SQL Database. You can choose other data connectors as well.

A dialog box will appear where you need to enter the Server and Database names provided by your Azure admin.

However, if your dataset is already in an Excel worksheet, select the range or table containing your data, making sure you include all rows and columns you want to clean.

Go to the Data tab and click From Table/Range to load the data into Power Query.

The Create Table dialog box will show up. Click OK to proceed.

Once in the Power Query Editor, choose the column where you want to remove extra spaces by clicking its header.

Right-click the selected column, choose Transform, and then click Trim to instantly remove all extra spaces, keeping only single spaces between words.

Review the preview in the editor to confirm that the spaces are cleaned correctly.

Now, click on the File tab in the Power Query Editor interface and choose Close & Load To from the context menu.

The Import Data dialog box will show up. There, select the Existing worksheet option and click a cell on the active worksheet to indicate where to import the dataset. Click OK.

Power Query will create an automatic query connectivity with the source dataset and export the cleaned dataset to the designated worksheet.

The next time you update the source data, go to the Queries & Connections navigation panel on the right side of the active worksheet.

Right-click on Table1 or whichever table it is in the navigation panel, and choose Refresh from the context menu.

Conclusions

So far, you have gone through five different methods to remove extra spaces from Excel.

These methods are suitable for different dataset sizes, data transformation needs, and your Excel expertise level.

Choose the method that suits you and write a few lines in the comment box, which one you have chosen and the reason behind your selection.

Also, don’t forget to share this guide with your friends and colleagues who also use Microsoft Excel.