Clean Messy Data With TRIM and PROPER

Imports from web forms and CSV files love trailing spaces and inconsistent capitalization. TRIM and PROPER fix most of it before lookups fail with #N/A.

Sample messy column

Column A is what you pasted. Column B will be cleaned.

TRIM — remove leading/trailing spaces

B2, fill down:

=TRIM(A2)

TRIM also collapses double spaces between words to one. It does not remove non-breaking spaces from some web exports — use CLEAN next if needed.

PROPER — capitalize words

C2:

=PROPER(B2)

Result: Widget, Gadget, Cable, Widget.

PROPER lowercases the rest of each word. For SKU codes like W-104, skip PROPER on those columns — TRIM only.

CLEAN — non-printing characters

If TRIM alone does not fix a value copied from a PDF:

=CLEAN(TRIM(A2))

Replace formulas with values

After cleaning:

1. Select B2:C100
2. Ctrl+C
3. Paste Special → Values (Excel: Ctrl+Alt+V, V, Enter)
4. Delete column A if you no longer need raw paste

Google Sheets: Copy → Paste special → Values only.

Why VLOOKUP breaks on spaces

Orders sheet E2: W-104 Products sheet A5: W-104 (trailing space)

=VLOOKUP(E2, Products!A:D, 4, FALSE)

Returns #N/A. Fix Products column A:

=TRIM(A2)

Paste values, then retry lookup. Full walkthrough: VLOOKUP basics.

Wrong vs Right

CSV import tip

Trim during import when possible: import CSV into Google Sheets cleanly.