Spreadsheet Text Functions

Text functions read and rebuild the contents of a cell. They matter for cleaning because the defects that break matching — padding, inconsistent case, several facts packed into one field — are invisible on screen and precise to describe in a formula. The core set is small: LEN measures, FIND and SEARCH locate, LEFT, RIGHT and MID extract, TRIM cleans, and CONCATENATE rejoins.

LEN turns an invisible problem into a number: a value that displays as six characters but reports eight is carrying padding. FIND returns the position of a character, which is what makes extraction adapt to rows of different lengths — =LEFT(A2,FIND(" ",A2)-1) takes everything before the first space regardless of how long it is, where a hard-coded count breaks on the first exception.

Two behaviors are worth knowing before they cost time. FIND is case-sensitive and fails with an error when the text is not found, while SEARCH ignores case; choosing the wrong one turns a clean extraction into #VALUE!. And TRIM, per Microsoft, “removes all spaces from text except for single spaces between words” — it strips the ends and collapses runs inside the text — but it “was designed to trim the 7-bit ASCII space character (value 32)” and leaves the non-breaking space, character 160, that arrives with anything copied from a web page. When TRIM appears to do nothing, that character is the usual reason, and SUBSTITUTE is the follow-up: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) first replaces each character 160 with an ordinary space, so TRIM can then remove it.

Two cautions about results. Extraction functions such as LEFT, RIGHT, and MID return text, so a date extracted from a longer string is still text until it is converted — sorting it would order it alphabetically. And they produce new values rather than repairing the originals, which means a cleaning column has to be reviewed and then committed deliberately, not left as a formula that recalculates when the source changes underneath it.

References:

• TRIM function
• Top ten ways to clean your data