Trim Spreadsheet Whitespace Without Damaging Real Values

A status that looks like Open may actually contain an ordinary trailing space. A company name copied from a web page may end with a non-breaking space. These invisible differences can create separate filter options, prevent joins and conceal duplicates in a published table.

Do not solve this by removing every space. Preserve spaces inside values such as New York, SW1A 1AA and source-faithful names. The safe workflow is: retain the raw column, create a cleaned column, review the changes, then publish the cleaned export.

Start with an audit column

Suppose column A contains status_raw. Add these helper columns:

Column	Formula in row 2	Purpose
Visible wrapper	="["&A2&"]"	Makes ordinary boundary spaces easier to see
Raw length	=LEN(A2)	Reveals values that look equal but have different lengths
Clean candidate	Use one of the methods below	Produces the proposed publication value
Changed?	=NOT(EXACT(A2,D2))	Flags every difference between raw and cleaned text

The brackets will not expose every Unicode whitespace character, but the length and exact-comparison columns help identify suspicious records. Filter Changed? to TRUE and inspect the affected rows rather than overwriting the source immediately.

A sample might produce this result. Here, ␠ and [NBSP] are visible audit notation, not the literal text to publish:

status_raw	status_clean	Decision
Open␠	Open	Remove trailing ordinary space
Closed[NBSP]	Closed	Remove trailing non-breaking space
In review	In review	Preserve until the field owner confirms whether two spaces are meaningful

Google Sheets: choose quick or controlled trimming

For a field in which repeated internal spaces are definitely errors, select the range and use Data → Data cleanup → Trim whitespace. Google says this removes leading, trailing and excessive spaces, but does not remove non-breaking spaces (Google Docs Editors Help). Because it can also collapse repeated spaces inside a value, it is not the right default for free text, codes or source-faithful names.

To remove only ordinary spaces and non-breaking spaces from the beginning and end of a text value, use a helper column:

=REGEXREPLACE(A2,"^[ "&CHAR(160)&"]+|[ "&CHAR(160)&"]+$","")

This leaves interior spacing unchanged. REGEXREPLACE replaces matching text and returns text, so retain the original column until you have checked the result (Google Docs Editors Help).

Google Sheets’ TRIM(A2) is shorter, but it removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to one. Google also notes that it does not trim non-breaking spaces (Google Docs Editors Help). Use it only where that normalization is intentional.

Excel: account for non-breaking spaces

Excel’s standard formula is:

=TRIM(A2)

However, Excel TRIM was designed for ASCII space character 32. Microsoft says it does not remove non-breaking space character 160, and its documented behavior leaves only single spaces between words (Microsoft Support).

For a controlled category field where both non-breaking spaces and repeated internal spaces are errors, use:

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

Do not use that formula on a column whose internal spacing must remain source-faithful. For boundary-only cleanup in Power Query, use a custom column such as:

Text.Trim([status_raw])

Power Query documents Text.Trim as removing leading and trailing whitespace while leaving the text between those boundaries intact (Microsoft Learn).

Decide which columns should change

Apply a written rule by field, not one cleanup operation to the whole workbook:

• Categories and workflow statuses: Trim boundaries; collapse internal spaces only if the allowed-value list requires it.
• Join keys and identifiers: Remove boundary whitespace only when the data specification excludes it. Preserve internal spaces and leading zeros.
• Names and titles: Retain the raw value and review changes. Whitespace cleanup is not a substitute for company-name normalization.
• Descriptions and quoted text: Preserve intentional repeated spaces and line breaks. Handle multiline fields according to the CSV quoting rules for line breaks.
• Fixed-width data: Do not trim before parsing; padding may define field boundaries.
• Whitespace-only cells: Decide whether they become empty values. Do not silently turn them into zero or an undocumented missing category; use a stated blank and missing-value policy.

Validate the cleaned export

Before publishing:

1. Filter the Changed? column and review every changed value when practical. For very large files, document the rule, inspect distinct before-and-after pairs and review a sample of repeated cases.
2. On columns expected to be unique, flag collisions in the cleaned column with =AND(D2<>"",COUNTIF($D$2:$D$1000,D2)>1). Investigate duplicates created by trimming.
3. Rerun joins and record the unmatched-key count before and after cleanup. A lower count is useful only if the newly matched records refer to the same entities.
4. Check category counts. Open and Open␠ should merge only when they mean the same status.
5. Export values rather than helper formulas, then reopen or re-import the finished CSV and repeat the key checks.
6. Publish only the sanitized columns intended for public viewing; do not upload sensitive raw data or audit fields.

The result should be one public-facing value per intended category or key, with no accidental boundary whitespace and no loss of spacing that belongs to the data.