If you want to learn how to remove spaces in Excel after text along with some neat tricks and tips, follow along with the proven approaches illustrated in this effortless Microsoft Excel tutorial.

Often you get raw data from data repositories or CRM software that contains inconsistencies in text formatting, alignment, etc. The most common problem is unnecessary spaces and hidden spaces in text entries. These irregularly formatted texts interfere with search and filtering, various Excel functions, Excel automation, and so on. Not to mention the overall file size sometimes becomes unnecessarily large if these redundant spaces aren’t handled at the data extraction level.

When you know how to remove spaces after text in Excel, you can address all the above issues and more that could arise due to redundant spaces.

I’ve tried and tested multiple approaches to shred spaces after text and have found that the following methods are the most intuitive and easy to follow. Let’s begin!

📒 Read More: 9 Ways to Reduce Excel File Size

Using the Flash Fill Command

Flash Fill offers an easy way to remove spaces after texts in columns of text. You can show Excel how to modify the text strings by editing a few cells in a column. Excel will learn the pattern and apply it in the rest of the column. The method involves using a helper column in your dataset.

In the above dataset, there are many unnecessary spaces after texts. Let me show you how to fix those using Flash Fill below.

Create a helper column to the right of the Employee Name column. Let’s say it’s the Helper Column 1.

In Helper Column 1, type in the employee names in the correct format for the first two to three cells.

now, click the column header text, and press Ctrl + E on the keyboard.

If Excel recognizes the editing pattern, it’ll instantly fill up the rest of the cells in column B with reference data from column A. Column B now contains texts without spaces after the last word or letter.

Select the content of the Helper Column 1, press Ctrl + C, select the first cell in column A, and press Ctrl + V to transfer edited data.

Delete the Helper Column 1.

Repeat the same steps for the Department column, like creating the Helper Column 2 on the right.

Edit a few leading cells and use the Flash Fill command.

Delete the Helper Column 2 once its utility is complete.

The Flash Fill command is available in the following Excel editions:

• Excel for the web
• Excel 2016 or later for Windows
• Excel 2016 or newer for Mac

You can’t try out this method if you’re not using any of the above Excel versions.

Using the Find and Replace Tool

The Find and Replace tool is suitable for only trailing spaces. If there are spaces between texts, those will be replaced too.

To use this method, select the input dataset, and press the Ctrl + H keys.

In the Find what field, type a space.

Keep the Replace with field empty.

Click the Replace All button.

Excel will delete all spaces including trailing ones. You’ll also get a confirmation message for how many characters were changed.

Using the CLEAN Function

Often, imported raw data might contain nonprintable characters. These characters don’t show up on normal cell view. Also, printers might not be able to print these characters or print incorrect values. You can only visualize these characters by selecting a cell and looking at the formula bar.

For instance, in the above dataset, there are nonprintable characters in all the cells through A2:A5. Though the cells don’t show these symbols, you can see large gaps between the first and last names.

So, if there are trailing spaces in your dataset caused by nonprintable characters use the CLEAN function.

Select a blank cell to the right of the input dataset. In the current exercise, it’s B2.

In B2, enter the following formula and hit Enter:

=CLEAN(A2)

Excel will remove all undesirable characters from the selected cell.

Now, select B2 and drag down the fill handle in column B until there is data in the adjacent cells of column A.

You’ll instantly remove the trailing nonprintable spaces from the input dataset.

To get text output from the calculated column B, copy the column using Ctrl + C and paste that into any free column by pressing Ctrl + Alt + V.

You’ll see the Paste Special dialog box. Select Values there and click OK.

Excel will paste the calculated values as text in the destination column.

You can now move this dataset as per your preference.

If you select a cell, you’ll no longer see the unprintable space character.

Microsoft has introduced the CLEAN function from Excel 2013. So, you won’t find the function if you’re using Excel 2010 or earlier editions.

Using the Excel TRIM Function

The TRIM function is the default Excel function for shredding all unnecessary spaces. It works on spaces before and after texts. Also, it won’t delete the standard single space between two words.

To use a TRIM function, highlight an empty cell adjacent to the source dataset. For instance, B2 in this tutorial.

Enter the following formula in the cell and hit Enter.

=TRIM(A2)

In the above formula, A2 is the source text.

Excel will remove trailing spaces from the selected cell.

Click on the cell again and drag down the fill handle to apply the same formula to the rest of the dataset.

The data you’ve got so far is in formula form. Copy the content of column B by pressing Ctrl + C.

Now, select the first cell of the source column, like column A, and press Ctrl + Alt + V to use Paste Special.

In the Paste Special dialog box, select Values, and click OK.

Excel will convert the calculated data into text values and paste those into column A.

You can now delete column B to reduce clutter.

The only downside of this function is it’s only available since Excel 2016. If you’re working on an earlier version, you won’t find it.

Using the Data Validation Tool

You can prevent users from entering redundant trailing spaces in texts in Excel using a Data Validation rule. So, you no longer need to remove unnecessary spaces after texts.

To practice this method, navigate to the worksheet where you’re creating a data entry form.

Select the cell where data entry operators might include texts with unnecessary trailing space. In this exercise, it’s A2.

Click on the Data Validation command in the Data Tools block of the Excel Data tab.

The Data Validation dialog box will open. Click on the Settings tab and select Custom in the Allow field.

The Formula field will show up below the Data field.

Inside the Formula field, enter the following formula:

=TRIM(A2)=A2

Now, navigate to the Error tab and enter preferred warning messages in the Title and Error message field.

Click OK to save the Error message.

Now, copy A2 using Ctrl + C.

Select the empty table or cell range where you want to apply this Data Validation rule. Press Ctrl + V to paste the rule.

If you try to add any extra space after a text in any of these cells, you’ll get an error message as shown in the above screenshot.

Using Power Query

If you use Power Query to connect, transform, combine, and shape datasets in Excel, you can also use the tool to remove spaces after texts. It has a built-in Trim function in the Transform tab that removes trailing spaces as well as redundant spaces from a column of text data.

To import an external dataset to Power Query Editor, click on the Get Data command in the Data tab of the Excel ribbon menu.

Then, hover the cursor over an appropriate option, like From Database, From Azure, From Power Platform, and so on.

An overflow menu will show up. From this context menu, choose the final data connector, like From Azure SQL Database, if you’ve selected Azure in the previous context menu.

Follow the on-screen instructions to import your data from thereon.

For the present tutorial, I’ve imported a worksheet dataset by using the From Table/Range command in the Data tab.

When you do this, don’t forget to click OK on the Create Table dialog box to see your dataset in Power Query Editor.

Now that your source dataset is in Power Query, select the column in the editor interface by clicking on the column header.

Click on the Split Column command and choose By Delimiter in the drop-down menu.

You’ll see the Split Column by Delimiter wizard. There, select Space as the delimiter and the Left-most delimiter option as the Split at setting. Click OK to apply these changes.

Power Query will split the source data column into two.

The first column should have no trailing or leading spaces.

Inconsistent spaces should be in the second column. Select that, go to the Transform tab, and click on the Text Column drop-down menu.

You should see the Format option there. Click on that and select Trim from the drop-down menu.

Power Query should apply the TRIM function on the selected column to delete all trailing and leading spaces.

By now, you’ve got two columns of text with no trailing spaces.

Select these columns, right-click, and choose Merge Columns from the context menu.

The Merge Columns wizard will show up.

Choose Space as the Separator and enter a preferred name in the New column name field. Click OK to merge columns.

Power Query will combine the separate columns into one.

Go to the File tab and click on the Close & Load To button.

You’ll see the Import Data dialog. Click on the Existing worksheet option and highlight a cell on the worksheet where you want to load datasets from Power Query.

Click OK to complete the data import process from Power Query to the Excel worksheet.

You can delete the old dataset table.

Congratulations! You’ve successfully learned how to remove spaces in Excel after text using Power Query.

Using Excel VBA

The Excel VBA-based automation to remove spaces in Excel after text is the easiest method you can learn. A macro created using a VBA script has the ability to guide you through visual queues and no need to code complex Excel automation programs.

If you already know Excel VBA, you can get started with the script later in this section. However, if you’re new to this feature, go through this tutorial first:

📒 Read More: How To Use The VBA Code You Find Online

Use the following script to create a VBA macro in your Excel worksheet:

Sub RemoveSpaces()
    Dim rng As Range
    Dim cell As Range
    Dim inputRange As String
    
    ' Show input box to select the range
    On Error Resume Next
    Set rng = Application.InputBox("Select the range of cells:", Type:=8)
    On Error GoTo 0
    
    If rng Is Nothing Then
        MsgBox "No range selected. Exiting..."
        Exit Sub
    End If
    
    ' Loop through each cell in the selected range
    For Each cell In rng
        ' Remove leading and trailing spaces
        cell.Value = Trim(cell.Value)
        ' Remove multiple spaces between words
        cell.Value = WorksheetFunction.Trim(cell.Value)
    Next cell
    
    ' Show completion message
    MsgBox "Spaces removed successfully!"
End Sub

When ready, press the Alt + F8 keys on the keyboard together to launch the Macro dialog box.

Select the RemoveSpaces macro in the dialog and hit Run to execute that.

As you run the macro, Excel will show a dialog box so you can select one or many cell ranges for processing.

Once you hit OK in the input dialog box, Excel instantly removes all trailing spaces from your text dataset.

📝 Note: Any changes made in an Excel worksheet using a VBA macro remove all Excel undo memories. So, you won’t be able to revert the changes you make using Excel VBA. Run a script after being absolutely sure that you can let go of the undo features.

Conclusions

So far, you’ve learned how to remove spaces in Excel after text using different Excel user interface tools, like Flash Fill and Find and Replace.

You’ve also seen how you can write manual Excel functions, like CLEAN and TRIM to replace trailing spaces with nothing.

Moreover, you’ve learned how to prevent trailing spaces when entering data manually in Excel using the Data Validation tool.

Finally, you’ve learned two advanced methods, like Power Query and Excel VBA that enable you to automate the process to a great extent.

Did you find anything useful in this Excel tutorial? Do you know a better way to achieve no trailing spaces in Excel? Comment below!