Text Functions: LEFT, RIGHT, MID, LEN, TRIM¶

Real-world data is messy: extra spaces, mixed case, codes glued together. Text functions clean and reshape it.

Messy text data being cleaned into tidy columns

Extracting Parts of Text¶

Function	Returns	`="EXCEL2026"`
`LEFT(t, n)`	First n chars	`LEFT(t,5)` → `EXCEL`
`RIGHT(t, n)`	Last n chars	`RIGHT(t,4)` → `2026`
`MID(t, start, n)`	n chars from position	`MID(t,6,4)` → `2026`
`LEN(t)`	Length	`LEN(t)` → `9`

Cleaning Functions¶

excel

5 lines

1=TRIM("  hello   world  ")   → "hello world"   (removes extra spaces)
2=CLEAN(text)                  → strips non-printable characters
3=UPPER("excel")              → "EXCEL"
4=LOWER("EXCEL")              → "excel"
5=PROPER("ada lovelace")      → "Ada Lovelace"

Tip

TRIM is the first thing to try when a VLOOKUP or = comparison "should" match but doesn't — invisible trailing spaces are a classic culprit.

Joining Text¶

excel

3 lines

1=A2 & " " & B2                → "Ada Lovelace"
2=CONCAT(A2, " ", B2)         → "Ada Lovelace"
3=TEXTJOIN(", ", TRUE, A2:A5) → "Ada, Grace, Alan, Katherine"

TEXTJOIN is the star: a delimiter, a flag to ignore empties, then the range.

Finding & Replacing Within Text¶

excel

4 lines

1=FIND("@", A2)              → position of @ (case-sensitive)
2=SEARCH("st", A2)          → position (case-insensitive, wildcards ok)
3=SUBSTITUTE(A2, "-", "/")  → swap every "-" for "/"
4=REPLACE(A2, 1, 3, "XXX")  → replace 3 chars from position 1

Splitting an Email into Parts¶

A: ada@dev.com
=LEFT(A2, FIND("@",A2)-1)            → "ada"
=MID(A2, FIND("@",A2)+1, 99)        → "dev.com"

Combine FIND (locate the @) with LEFT/MID to slice text at a marker — a pattern you'll reuse endlessly. (Or just use Flash Fill from Chapter 3!)