TRIM, CLEAN, and non-breaking spaces

Why this lesson matters: TRIM, CLEAN, and non-breaking spaces

Remove common hidden characters and inconsistent spacing. In this module, the skill supports a larger goal: Standardise imported text and extract reliable codes, names, and categories. The practical application is to prepare copied web data for matching and import.

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

Build it step by step

Work from the business question toward the formula or tool. Keep the source data separate from the output and make every important assumption visible to the reviewer.

• Preserve the original imported field.
• Apply one cleanup or extraction rule per helper column.
• Compare row counts and inspect values that do not follow the expected pattern.

Check the result and common failure points

Keep a raw-data column and count blanks, unexpected lengths, and unresolved patterns after cleaning. Also test a known record, a missing or blank value, and a boundary case. Confirm that ranges are the same size, units are consistent, and copied references move only where intended.

• Compare at least one result with a manual calculation or filtered source record.
• Test blanks, zeros, duplicates, errors, and the exact rule boundary.
• Reconcile row counts or totals before relying on the output.

Use the result in a decision

Prepare copied web data for matching and import. Label the output clearly, state its period and unit, and explain the action it supports. A technically correct result is only complete when another person can understand, refresh, and review it.