How to Trim Spaces in Excel? (5 Easy Methods)

Sharing is caring!

Cleaning up text data is essential when working with spreadsheets. One of the most common issues users face in Excel is unwanted spaces in their data. These extra spaces can cause problems in sorting, filtering, and analyzing information.

Fortunately, Excel provides several powerful functions and methods to trim spaces efficiently. In this guide, we’ll explain how to use each technique step by step.

Common Types of Extra Spaces in Excel

There are different types of spaces that may appear in your data:

• Leading spaces – before the text
• Trailing spaces – after the text
• Extra spaces between words – more than one space
• Non-breaking spaces (ASCII code 160) – usually found in web-scraped data

Each type may require a specific method for removal.

1) Using the TRIM Function to Remove Extra Spaces

The TRIM function is the most straightforward way to remove leading, trailing, and extra spaces between words, keeping only single spaces where appropriate.

Syntax

=TRIM(text)

Example

=TRIM(A1)

This formula will clean the text in cell A1.

Steps to Use TRIM in a Column

1. Insert a new column next to your original data.
2. In the first row of the new column, type:

=TRIM(A2)

(Assuming your data starts at A2.)
3. Press Enter.
4. Drag the fill handle down to apply the formula to the rest of the rows.
5. Copy the cleaned data.
6. Right-click the original column and choose Paste Special → Values to overwrite it.

Important: TRIM does not remove non-breaking spaces.

2) Removing All Spaces Using the SUBSTITUTE Function

Sometimes, you want to delete every space, including the ones between words. The SUBSTITUTE function is perfect for this case.

Syntax

=SUBSTITUTE(text, old_text, new_text)

Example

=SUBSTITUTE(A1, " ", "")

This will eliminate all space characters in cell A1.

3) Removing Only Leading Spaces

If you need to remove only the leading spaces while preserving others, use a combination of MID, FIND, and TRIM functions.

Formula

=MID(A2, FIND(MID(TRIM(A2),1,1), A2), LEN(A2))

How It Works

• TRIM(A2) finds the first character without spaces.
• FIND(...) locates its position in the original string.
• MID(...) extracts the remaining text starting from that character.

This method keeps spaces between words intact and only removes the initial spaces.

4) Handling Non-Breaking Spaces

Sometimes, especially when data is copied from websites or PDFs, it contains non-breaking spaces instead of regular spaces. These won’t be removed by TRIM alone.

Use the following combined formula:

Formula

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

Explanation

• CHAR(160) represents the non-breaking space.
• SUBSTITUTE(...) converts them to regular spaces.
• TRIM(...) then removes unnecessary spaces.

This ensures even hidden or special characters are addressed.

5) Automating Space Trimming with Excel Add-ins

For bulk operations or more complex data sets, Excel Add-ins like Ablebits Trim Spaces can be extremely helpful. These tools offer:

• One-click removal of all types of spaces
• Handling of non-breaking characters
• In-place editing without writing formulas

Key Features of Add-ins Like Ablebits

• Remove leading, trailing, extra, and non-breaking spaces
• Batch process large ranges
• User-friendly interface

While add-ins are not free, they save a lot of time for those who manage large data sets.

Overview of Methods to Trim Spaces in Excel

Here is a quick comparison of various methods:

Converting Trimmed Text to Numbers

If your data contains numbers with spaces (e.g., ” 123 “), Excel treats them as text. After trimming, wrap the formula in VALUE to convert it back to a number.

Formula

=VALUE(TRIM(A2))

Now, Excel will recognize it as a proper numeric value, allowing calculations.

Final Thoughts

Learning how to trim spaces in Excel is an essential skill for data cleaning and spreadsheet efficiency. Whether you’re using built-in functions or third-party tools, removing unwanted spaces will make your data accurate, reliable, and ready for analysis.

Stick to the method that fits your situation best, and always verify results by spot-checking cleaned data. With the right approach, Excel becomes a far more powerful tool for productivity and accuracy.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.