C# Excel 程式庫

Explore IronXL C# .NET 的 Excel 試算表程式庫開始免費試用

無需 Interop 即可讀取 Excel 檔案

using IronXL;
using System;
using System.Linq;

// Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Select worksheet at index 0
WorkSheet workSheet = workBook.WorkSheets[0];

// Get any existing worksheet
WorkSheet firstSheet = workBook.DefaultWorkSheet;

// Select a cell and return the converted value
int cellValue = workSheet["A2"].IntValue;

// Read from ranges of cells elegantly.
foreach (var cell in workSheet["A2:A10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

// Calculate aggregate values such as Min, Max and Sum
decimal sum = workSheet["A2:A10"].Sum();

// Linq compatible
decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronXL;
using System;
using System.Linq;

// Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Select worksheet at index 0
WorkSheet workSheet = workBook.WorkSheets[0];

// Get any existing worksheet
WorkSheet firstSheet = workBook.DefaultWorkSheet;

// Select a cell and return the converted value
int cellValue = workSheet["A2"].IntValue;

// Read from ranges of cells elegantly.
foreach (var cell in workSheet["A2:A10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

// Calculate aggregate values such as Min, Max and Sum
decimal sum = workSheet["A2:A10"].Sum();

// Linq compatible
decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

PM >Install-Package IronXL.Excel

Install-Package IronXL.Excel

EXCEL 工具

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

Curtis Chau

Updated: 2026年8月16日

Unwanted spaces in Excel can cause formula errors, break VLOOKUP references, and mess up data sorting. Whether you import data from web pages, copy data from external sources, or handle human data entry, extra spaces are a common nuisance.

In this tutorial, you will learn every effective method to edit your Excel files and remove spaces, from simple built-in tools to advanced formulas, Power Query, and programmatic solutions.

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.

Common Problems Caused by Extra Spaces

Formula Errors: VLOOKUP, XLOOKUP, or MATCH formulas fail because "John Doe " does not equal "John Doe".
Incorrect Calculations: Functions like COUNTIF or SUMIF miscalculate because extra blanks create separate unique categories.
Sorting & Filtering Glitches: Sorting alphabetically places entries with leading spaces at the very top, breaking your expected layout.

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.

How to Mass Delete Blank Spaces

Highlight the selected cells or the whole column containing extra spaces.
Press Ctrl + H to open the Find & Replace dialog box.
In the Find what field, press the Spacebar once to enter one space.
Leave the Replace with field completely empty.
Click Replace All.

提示: If you only want to remove spaces between words while retaining single spaces, do not use Find & Replace, as it deletes every single space in the range.

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.

How the TRIM Function Works

Removes leading spaces: Deletes all spaces before the first character.
Removes trailing spaces: Deletes every space after the last character.
Fixes spaces between words: Reduces multiple spaces down to single spaces.

Step-by-Step Formula Implementation

Initial Cell with Empty Space

Cell with empty cell

Insert a helper column next to your original column.
In the first cell of the helper column (e.g., B2), enter the formula:
```
=TRIM(A2)
```
```
=TRIM(A2)
```
Text
Press Enter to execute the formula.
Drag the fill handle down to apply the formula across the entire column.
Copy the newly cleaned range, right-click the original column, and choose Paste as Values to save the clean text string over the raw data.

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.

Formula to Remove Non-Breaking Spaces and Extra Spaces

To clean non-breaking spaces, combine the SUBSTITUTE function, CHAR function, and TRIM function:

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

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

Text

Adding the formula

How This Formula Works

Output

SUBSTITUTE(A2, CHAR(160), " "): Finds non-breaking spaces using their code number (160) and replaces them with standard single spaces.
CLEAN(...): Uses the CLEAN function to strip out non-printable characters and line breaks (ASCII 0–31).
TRIM(...): Strips out all remaining leading and trailing spaces and fixes spaces between words.

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.

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

Method Comparison Table

Method	Leading & Trailing Spaces	Multiple Spaces Between Words	Non-Breaking Spaces (ASCII 160)	All Spaces
Find & Replace	Deletes	Deletes	No	Deletes
TRIM Function	Removes	Reduces to 1	No	No
TRIM + SUBSTITUTE	Removes	Reduces to 1	Removes	No
SUBSTITUTE Function	Deletes	Deletes	Manual	Deletes
Power Query	Removes	Retains	Handles via script	Optional

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.

Cleaning Cell Strings Programmatically

Using IronXL in C#, you can iterate through cell ranges to strip out unwanted spaces, non-printable characters, and formatting errors on server environments:

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");

Text

IronXL Output

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