Day 5 — Cleaning text: TRIM, LEFT, RIGHT, MID

Exported data arrives messy. These are the functions that make it usable.

The problem

This lesson uses the Raw sheet below — deliberately messy, the way exported data really arrives.

Look at column A. Extra spaces at the start, double spaces in the middle, inconsistent capitals. This is what real exports look like, and none of it will match, sort or group correctly until it is cleaned.

LEN — how long is it really?

A2 looks like "amit sharma" — 11 characters. LEN says 14. The other three are invisible spaces, and they are why a lookup on this cell would fail.

LEN is your first diagnostic When a VLOOKUP returns #N/A on values that look identical, compare their lengths. Nine times out of ten one has a trailing space.

TRIM — remove the extra spaces

TRIM removes spaces from both ends and collapses runs of spaces inside down to one. A4 is "ravi kumar" with a double space; TRIM makes it single. The bracket trick is how you see whitespace that is otherwise invisible.

UPPER, LOWER, PROPER

PROPER capitalises the first letter of every word. Combined with TRIM it turns " amit sharma " into "Amit Sharma" in one step — the standard cleaning formula.

PROPER is not clever It will happily produce "Mcdonald" and "Ravi Kumar Iii". For names it is a good start, not a finished answer.

LEFT and RIGHT

=LEFT(text, how many characters) =RIGHT(text, how many characters)

Column B holds codes like DL-2024-0912. The first two characters are the state, the last four the serial number.

MID — from the middle

=MID(text, start position, how many characters)

Counting starts at 1, so the year in DL-2024-0912 begins at position 4.

Fixed positions are fragile =MID(B2,4,4) only works because every code has a two-letter state prefix. One code with a three-letter prefix and every row below is wrong. Tomorrow you will learn FIND, which locates the separator instead of assuming where it is.

Putting it together: cleaning a name column

One formula, copied down, turns an unusable column into a clean one. In a real sheet you would then copy the results and Paste Special → Values over the original.

Order matters when you nest

Rows 1 and 2 agree, because upper-casing and taking the first four characters do not interfere. Rows 3 and 4 do not agree at all: trimming first gives "amit", trimming afterwards gives just "am" — the first two characters were spaces and got thrown away. Clean first, then cut.

Try these yourself

Find the true length of A5 and work out how many spaces TRIM would remove.
Turn A6 into a properly capitalised name.
Pull the state code (first two characters) out of B4.
Pull the four-digit year out of B5.
Build the string MH-0765 from B6 using LEFT and RIGHT joined with &.

← PreviousDay 4 — COUNTIF, SUMIF and the IFS family Next →Day 6 — FIND, SUBSTITUTE and splitting text