How to Remove Space After Text in Excel (6 Effective Ways)

Extra spaces after text entries in Excel can cause issues with sorting, filtering, or using lookup formulas. These trailing spaces often sneak in from copy-pasted data or inconsistent manual entries, especially in large datasets. However, Excel offers several ways to clean them up.

In this article, we’ll learn multiple methods to remove spaces after text in Excel, using simple formulas, Find & Replace, Power Query, and even VBA. Each method works in different scenarios depending on how your data is structured. Let’s get started.

This method is ideal for bulk cleaning when you’re dealing with obvious or consistent trailing spaces at the end of your text entries. It’s especially useful when you’re working with imported or manually typed data that often includes extra whitespace. However, since Find and Replace doesn’t distinguish between types of spaces (like those between words or at the end), you should only use this method if you’re confident the trailing spaces are redundant and not part of actual content.

We’ll use a sample dataset containing a list of names where each entry has invisible trailing spaces after the text.

Steps:

➤ Select the range of cells that contain the text values with trailing spaces, for example, A2:A11.

➤ Press  Ctrl  +  H  to launch the Find and Replace dialog box.
➤ In the Find what box, press the Spacebar several times to match the excess spaces you want to remove.
➤ Optionally, If you’re dealing with non-breaking spaces, press  Alt  +  255  instead.
➤ Leave the Replace with box completely empty.
➤ Click on Replace All.

Excel will remove all instances of the space(s) you entered including trailing ones. If your dataset includes names or text with valid inner spacing (like “John Smith“), double-check that no meaningful spacing is lost.

When your Excel data includes names or words with unpredictable spacing, especially those copied from web pages or forms, the TRIM function is a dependable solution. It’s designed to clean up text by removing all leading, trailing, and extra internal spaces, while still keeping a single space between words intact. This makes it perfect for cleaning messy names, labels, or entries without destroying meaningful spacing.

Steps:

➤ Click on an empty helper column (e.g., B2) right next to your text.
➤ Enter the formula:

➤ Press Enter, then drag the formula down to clean the rest of the dataset.

➤ Alternatively, try this variation if you want to remove trailing spaces without affecting internal spaces:

It ensures that only actual characters are preserved, trimming just the unwanted padding at the end.

When you know exactly how many extra spaces or unwanted characters appear consistently at the end of your text strings, the LEFT function offers a simple, direct way to remove them. This method works best if the number of trailing spaces is fixed across all cells, allowing you to precisely cut off the unwanted characters without relying on dynamic trimming.

Steps:

➤ Suppose every cell in column A ends with exactly 3 trailing spaces. Use this formula in a helper column (e.g., B2):

➤ Press Enter for output.
➤ Drag the formula down to apply it to all rows.

Now this formula extracts the left portion of the text, excluding the last 3 characters (the unwanted spaces).

When you want to eliminate specific patterns of extra spaces such as double or multiple spaces inside or at the end of text strings, the SUBSTITUTE function is a versatile tool. Unlike TRIM function, which removes all leading and trailing spaces and reduces internal spaces to a single space, SUBSTITUTE lets you precisely target and replace repeated spaces or other unwanted character sequences.

Steps:

➤ To remove all instances of double spaces in cell B2, enter this formula:

➤ Press Enter for output.
➤ Drag the formula down to apply it across your dataset.

Now you’ll find all extra spaces removed from all parts of your text.

When working with large datasets or frequently importing data from external sources, manually cleaning text can become tedious and error-prone. Power Query offers an efficient and automated way to cleanse your data, including removing unwanted spaces before and after text entries. It’s especially useful for recurring data cleaning tasks because once set up, you can refresh the query anytime new data arrives, saving time and ensuring consistent results.

Steps:

➤ Select any cell within your dataset.
➤ Navigate to the Data tab and click From Table/Range to open Power Query Editor. In the pop-up dialog, check your headers and click OK.

➤ In Power Query Editor, select the column containing the text you want to clean.
➤ Go to the Transform tab >> Click Format under Text Column drop-down and then select Trim.

➤ Once spaces are removed, click Close & Load to load the cleaned data back into Excel.

Now your data will be loaded into a new sheet with trimmed extra spaces.

When dealing with large volumes of data that require fast, in-place cleaning, VBA macros offer a highly efficient solution. Unlike formula-based methods or Power Query, which may create new output tables, a VBA macro can directly modify the selected cells without extra steps. This makes it ideal for users comfortable with macros who want a quick, repeatable way to strip unwanted spaces from many cells in seconds.

Steps:

➤ Select the range of cells that contain the text values with trailing spaces, for example, A2:A11.

➤ Press  Alt  +  F11  to open the VBA Editor.
➤ Click Insert tab >> Module to add a new module.
➤ Paste the following code:

Sub RemoveSpacesafterText()
    Dim M As Range
    Dim cell As Range
    Set M = Selection
    For Each cell In M
        If Not IsEmpty(cell) Then
            cell = Trim(cell)
        End If
    Next cell
End Sub

➤ Return to Excel and select the range of cells you want to clean.
➤ Press  F5  or click Run in the VBA Editor to execute the macro.

This macro processes each selected cell, removing both leading and trailing spaces directly within the cells themselves. It’s an excellent technique when you want to quickly tidy up large datasets without generating new columns or tables.

Frequently Asked Questions

What’s the fastest way to remove trailing spaces in Excel?

The TRIM function is usually the best choice. It removes all leading and trailing spaces from a cell while keeping single spaces between words intact. It’s simple, efficient, and works instantly on most datasets.

Will Find & Replace remove spaces only at the end?

No, Find & Replace tool removes all space characters it matches regardless of whether they appear at the start, middle, or end. If you only want to remove trailing spaces, TRIM function or VBA would be safer alternatives.

How do I remove a fixed number of spaces after text?

You can use a formula like =LEFT(A2,LEN(A2)-n) where n is the number of characters you want to remove. This helps when you know the exact number of trailing characters to discard.

Can Power Query remove trailing spaces automatically?

Yes, Power Query is great for bulk cleaning. Use the Format >> Trim option in Power Query Editor which removes both leading and trailing spaces, making it ideal for large or inconsistent data sets.

Wrapping Up

In this tutorial, we learned multiple ways to remove spaces after text in Excel. Whether you’re cleaning entries with TRIM, removing fixed padding with LEFT, or processing bulk data using Power Query or VBA, each method has its purpose. Choose the one that fits your data situation best to keep your sheets clean and consistent. Feel free to download the practice file and share your feedback.