Day 17: Text Functions & Data Cleaning

TRIM removes all leading spaces, trailing spaces, and extra spaces between words (reduces multiple spaces to one). =TRIM(A2). Leading and trailing spaces are invisible but cause VLOOKUP #N/A errors, COUNTIF mismatches, and grouping issues in Pivot Tables. They are extremely common in data exported from databases or entered by users. TRIM should be one of the first cleaning steps on any text column used as a lookup key.

CLEAN removes non-printable characters (ASCII values 0–31) from text. These are control characters that sometimes appear when importing data from external systems — they are invisible but cause string comparison failures. =CLEAN(A2). Use both TRIM and CLEAN together on imported data: =TRIM(CLEAN(A2)). If your VLOOKUP or EXACT function still fails after TRIM, apply CLEAN as well.

Using formulas: First Name: =LEFT(A2, FIND(" ",A2)-1). Last Name: =MID(A2, FIND(" ",A2)+1, LEN(A2)). This works for two-part names. For three-part names, use FIND with a start position for the second space. Alternatively: Data → Text to Columns → Delimited → Space delimiter → separates into multiple columns instantly. Or in Power Query: Transform → Split Column → By Delimiter → Space — more flexible and handles inconsistent spacing.

UPPER(text) converts to ALL CAPS. LOWER(text) converts to all lowercase. PROPER(text) converts to Title Case (capitalises first letter of each word). TRIM removes extra spaces. Combined: =PROPER(TRIM(A2)) standardises to "North" from any variation. For replacing specific wrong values: =SUBSTITUTE(A2,"NRTH","North") replaces one string with another (case-sensitive). For bulk standardisation in Power Query: Transform → Replace Values → set up each replacement, or use a mapping table and merge.

SUBSTITUTE replaces a specific text string with another: =SUBSTITUTE(A2," Ltd","") removes " Ltd" from company names. It is content-aware — it finds the text wherever it appears. You can specify which occurrence to replace with the fourth argument: =SUBSTITUTE(A2,"-","",2) removes the second hyphen only. REPLACE replaces characters at a specific position and length: =REPLACE(A2,1,3,"") removes the first 3 characters regardless of content. Use SUBSTITUTE when you know what to replace; use REPLACE when you know where to replace.