← Google Sheets from Scratch: Formulas, QUERY, and Collaboration
Lesson
Lesson 2: Text functions and data cleanup: TRIM, SPLIT, CONCATENATE/“&”, LEFT/RIGHT/MID, TEXTJOIN
Clean up “dirty” text data: remove extra spaces, split and join values, and cut out parts of a string.
Text functions: cleanup and transformation
Google Sheets text functions
Data from exports and CRM systems often arrives “dirty”: extra spaces, several values in one cell, names stuck together or split apart. Text functions help you clean it all up.
=TRIM(text) removes extra spaces at the start, at the end, and inside a string, leaving exactly one space between words. Extra spaces are a common reason VLOOKUP “can’t find” a value: one cell says “Ivanov”, another says “Ivanov ” (with a space), and they don’t match. TRIM solves this.
=SPLIT(text, delimiter) splits a string into several cells. Example: =SPLIT("Moscow, 5 Lenin St.", ", ", FALSE) gives “Moscow” and “5 Lenin St.” in neighboring cells (FALSE: split on “, ” as a whole). Joining works the other way: =CONCATENATE(A1, " ", B1), or the shorter =A1&" "&B1, combines several values into one. =TEXTJOIN(delimiter, ignore_empty, range) is handy for joining a whole range: =TEXTJOIN(", ", TRUE, A1:A5) collects all the non-empty cells, separated by commas.
To cut out part of a string: =LEFT(text, n) gives the first n characters; =RIGHT(text, n) gives the last n characters; =MID(text, start, length) gives a substring of length characters, starting at position start. Example: =LEFT("AB-2024-001", 2) returns “AB”; =MID("AB-2024-001", 4, 4) returns “2024”.
Lesson notes
Google Sheets text functions
Data from exports and CRM systems often arrives “dirty”: extra spaces, several values in one cell, names stuck together or split apart. Text functions help you clean it all up.
=TRIM(text) removes extra spaces at the start, at the end, and inside a string, leaving exactly one space between words. Extra spaces are a common reason VLOOKUP “can’t find” a value: one cell says “Ivanov”, another says “Ivanov ” (with a space), and they don’t match. TRIM solves this.
=SPLIT(text, delimiter) splits a string into several cells. Example: =SPLIT("Moscow, 5 Lenin St.", ", ", FALSE) gives “Moscow” and “5 Lenin St.” in neighboring cells (FALSE: split on “, ” as a whole). Joining works the other way: =CONCATENATE(A1, " ", B1), or the shorter =A1&" "&B1, combines several values into one. =TEXTJOIN(delimiter, ignore_empty, range) is handy for joining a whole range: =TEXTJOIN(", ", TRUE, A1:A5) collects all the non-empty cells, separated by commas.
To cut out part of a string: =LEFT(text, n) gives the first n characters; =RIGHT(text, n) gives the last n characters; =MID(text, start, length) gives a substring of length characters, starting at position start. Example: =LEFT("AB-2024-001", 2) returns “AB”; =MID("AB-2024-001", 4, 4) returns “2024”.