Skip to content

Text & dates · Easy

Which text functions do you use most for cleaning data?

Short answer

TRIM removes extra spaces, CLEAN strips non-printing characters, UPPER/LOWER/PROPER fix case, LEFT/RIGHT/MID extract parts, TEXTBEFORE/TEXTAFTER split on delimiters, SUBSTITUTE replaces text, and TEXTJOIN combines values. VALUE converts numeric text into numbers.

Detailed answer

Example: extract the domain from an email.

=TEXTAFTER(LOWER(TRIM(A2)), "@")

Older Excel: =MID(A2, FIND("@", A2) + 1, 100).

Non-breaking spaces from web exports survive TRIM — remove them with SUBSTITUTE(A2, CHAR(160), " ").