Data Parsing and Cleansing
Cleaning before transforming
The Data Cleansing tool is the fast path for the most common fixes. Its checkboxes let you remove null rows and null columns (these two act on the whole dataset), and, for the fields you select, replace nulls with a value, remove leading/trailing whitespace, remove tabs and line breaks, remove duplicate whitespace, remove punctuation, and modify case. Remember the split: only the null row/column removal is dataset-wide; everything else applies to selected fields.
For anything the checkbox list does not cover, use the Formula tool with string functions such as Trim, Left, Right, Substring, FindString, Replace, GetWord, Length, and, importantly, RegEx_Replace. The RegEx tool offers Match, Parse, and Replace modes; Text To Columns splits one field into several on a delimiter; the DateTime tool converts between strings and Date/DateTime types, and DateTimeParse/DateTimeFormat in a Formula give you full control of the pattern.
Traps
Null, empty string, and a single space are three different things. IsNull() is not IsEmpty(), and trimming a numeric field does nothing - convert it to a string first. Data Cleansing does not change field types. Also confirm whether case changes are wanted before joining on that field.
Worked example
Phone numbers arrive as (555) 123-4567. Cleaning punctuation only handles parentheses; to get a pure 10-digit value, use RegEx_Replace([Phone], "[^0-9]", ""), which strips every non-digit in one step.