About This Article
This article was created using an automated generation workflow utilizing generative AI. It is organized as a tip for cleaning up strings within an Excel table in bulk, based on reviewing the Microsoft Learn Office Scripts Range API.

Verification Status: 📘 Official Specifications Confirmed · Office Scripts Sample Implemented · Not Tested on Physical Hardware in Target Microsoft 365 Environment

Clean Up Leading/Trailing and Full-Width Spaces in Excel: Fixing Inconsistencies with Office Scripts

Excel files collected from people often contain space inconsistencies like “Tokyo”, ” Tokyo”, “Tokyo　”, and “Tokyo Headquarters”. Although they look identical, they can be treated as completely different strings during aggregation and matching.

With Office Scripts, you can easily write a process that scans and formats only the text strings within a table.

Formatting Only Text Strings

// Extract the cell value as a string.
const before = String(values[row][col]);

// 1) Convert full-width spaces to half-width
// 2) Convert consecutive spaces to a single space
// 3) Remove leading and trailing spaces
const after = before
  .replace(/u3000/g, " ")
  .replace(/s+/g, " ")
  .trim();

// Write back only cells whose values have changed.
// This prevents unnecessary rewrites of cells with no changes.
if (before !== after) {
  range.getCell(row, col).setValue(after);
}

Through these three steps, full-width spaces are converted to half-width, consecutive spaces are reduced to one, and leading/trailing spaces are removed.

Do Not Touch Formula Cells

If you overwrite the entire table using setValues(), there is a risk of replacing formula cells with static values. Therefore, in the complete version, we also check getFormulas() and skip any cells containing formulas.

This is where we prioritized “code that doesn’t break things” over “short code”.

What Is This Effective For?

• Inconsistent department names and employee names

• Cleaning up spaces after CSV import

• Pre-processing before VLOOKUP / XLOOKUP

• Normalization before duplicate checks

• Formatting before passing data to Power Automate

flowchart LR
    A[Excel Table] --> B[文字列セルだけ取得]
    B --> C[全角/連続/前後空白を正規化]
    C --> D[変更セルだけ書き戻す]

GitHub Sample

• Format strings in a table using Office Scripts

Official Information and Primary Sources

• ExcelScript.Range interface – Microsoft Learn