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.

Prerequisites

• Excel on the Web (Office Scripts are supported in Excel for the Web and newer desktop versions for Windows/Mac with an eligible Microsoft 365 commercial or education plan).

• A target worksheet containing an Excel Table (created via Insert > Table).

Complete Office Scripts Code

function main(workbook: ExcelScript.Workbook) {
  // Get the active worksheet
  let sheet = workbook.getActiveWorksheet();

  // Get the first table on the sheet (adjust name or use getUsedRange() if needed)
  let tables = sheet.getTables();
  if (tables.length === 0) {
    console.log("No table found on this worksheet.");
    return;
  }
  let table = tables[0];

  let range = table.getRange();
  let values = range.getValues();
  let formulas = range.getFormulas();

  for (let row = 0; row < values.length; row++) {
    for (let col = 0; col < values[row].length; col++) {
      // Skip formula cells to avoid overwriting formulas with static values
      const formula = String(formulas[row][col]);
      if (formula.startsWith("=")) {
        continue;
      }

      const cellVal = values[row][col];
      // Process only string values
      if (typeof cellVal === "string" && cellVal.length > 0) {
        const before = cellVal;

        // 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.
        if (before !== after) {
          range.getCell(row, col).setValue(after);
        }
      }
    }
  }
}

Through these steps, full-width spaces are converted to half-width, consecutive spaces are reduced to one, and leading/trailing spaces are removed safely without touching formula cells.

Do Not Touch Formula Cells

If you overwrite the entire table using setValues() without checking formulas, there is a risk of replacing formula cells with static values. Therefore, we check getFormulas() and skip any cells starting with =.

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