TRIM & text cleanup — Excel guide
Scrubbing imported data: invisible characters, inconsistent case, and formatting numbers into presentable text.
TRIM
Strip leading/trailing spaces and collapse inner runs to one.
- CLEAN removes non-printing characters (line breaks); TRIM(CLEAN(x)) is the standard wash.
- Cleaning inline beats a helper column for lookups: XLOOKUP(TRIM(A2), keys, values).
SUBSTITUTE
Replace text by MATCHING it (REPLACE works by position instead).
- The 4th argument targets only the Nth occurrence.
- Count characters: LEN(A2) - LEN(SUBSTITUTE(A2, ",", "")) = how many commas.
- UPPER / LOWER / PROPER normalize case — but PROPER flattens McDONALD to Mcdonald.
TEXT
Format a number INTO a string using a format code.
- Format codes mirror custom number formats: "0.0%", "#,##0", "000" (leading-zero padding), "dddd" (day name).
- The result is text — great in labels, dead to math.
- TEXTSPLIT (365) is Text-to-Columns as a live formula.