How to Remove Spaces in Excel: .NET10 Guide (Methods & Formulas)

Why Unwanted Spaces Cause Issues in Excel

Spaces in Excel aren't always visible, but Excel treats a space as an actual character. Even a single trailing space can turn a matching formula into a frustrating error.

Method 1: The Quickest Way (Find & Replace Feature)

If you need to quickly remove all spaces in selected cells, Excel's Find & Replace tool is the fastest route.

Method 2: Using the TRIM Function (Standard Cleaning)

The TRIM function is designed specifically to remove leading and trailing spaces from a text string while leaving single spaces between words intact.

Method 3: Removing Non-Breaking Spaces (TRIM + SUBSTITUTE)

Does TRIM() remove all spaces? No. A common issue when you import data or copy data from web pages is the presence of non-breaking spaces (ASCII code 160). The standard TRIM function only recognizes standard spaces (ASCII code 32) and will completely ignore non-breaking spaces.

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

Method 4: Removing Spaces via Power Query

For recurring data imports or large datasets, Power Query automates the data cleaning process without relying on complex cell formulas.

1. Select your data range or table.
2. Go to the Data tab and click From Table/Range.
3. Right-click the column header you wish to clean.
4. Select Transform > Trim to remove leading and trailing spaces.
5. Select Transform > Clean to strip out invisible characters and line breaks.
6. Click Close & Load to return the cleaned dataset into a new worksheet.

Method Comparison Table

For Developers: Automating Excel Space Removal with IronXL

While manual ribbon commands and cell formulas work well for desktop spreadsheets, automating batch updates across thousands of workbooks requires a developer-focused tool. IronXL is a robust .NET spreadsheet processing library that lets C# and VB.NET developers clean Excel files programmatically without requiring Microsoft Excel to be installed.

using IronXL;

// Load an existing workbook containing raw imported data
WorkBook workbook = WorkBook.Load("ImportedData.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;

// Loop through rows in a target column to clean up text strings
foreach (var cell in worksheet["B2:B100"])
{
    if (cell.Value != null)
    {
        string rawText = cell.Value.ToString();

        // Remove non-breaking spaces (ASCII 160) and standard extra spaces
        string cleanedText = rawText.Replace((char)160, ' ').Trim();

        // Update the cell value
        cell.Value = cleanedText;
    }
}

// Save the updated, clean workbook
workbook.SaveAs("CleanedData.xlsx");

using IronXL;

// Load an existing workbook containing raw imported data
WorkBook workbook = WorkBook.Load("ImportedData.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;

// Loop through rows in a target column to clean up text strings
foreach (var cell in worksheet["B2:B100"])
{
    if (cell.Value != null)
    {
        string rawText = cell.Value.ToString();

        // Remove non-breaking spaces (ASCII 160) and standard extra spaces
        string cleanedText = rawText.Replace((char)160, ' ').Trim();

        // Update the cell value
        cell.Value = cleanedText;
    }
}

// Save the updated, clean workbook
workbook.SaveAs("CleanedData.xlsx");

Imports IronXL

' Load an existing workbook containing raw imported data
Dim workbook As WorkBook = WorkBook.Load("ImportedData.xlsx")
Dim worksheet As WorkSheet = workbook.DefaultWorkSheet

' Loop through rows in a target column to clean up text strings
For Each cell In worksheet("B2:B100")
    If cell.Value IsNot Nothing Then
        Dim rawText As String = cell.Value.ToString()

        ' Remove non-breaking spaces (ASCII 160) and standard extra spaces
        Dim cleanedText As String = rawText.Replace(Chr(160), " "c).Trim()

        ' Update the cell value
        cell.Value = cleanedText
    End If
Next

' Save the updated, clean workbook
workbook.SaveAs("CleanedData.xlsx")

Summary

Removing unwanted spaces in Excel is essential for accurate calculations, error-free formulas, and clean data analysis. You can quickly delete all spaces using Find & Replace, eliminate excess spaces while preserving word separation using the TRIM function, or handle complex web data imports by combining TRIM, CLEAN, and SUBSTITUTE to clear non-breaking spaces (ASCII 160).

Streamline Your Excel Workflows with IronXL

Need to automate spreadsheet operations, clean high-volume data, or manage Excel reports directly within your .NET applications? IronXL simplifies workbook manipulation with clean, intuitive C# code.

Start Your IronXL Free Trial Today