Ten people typed into your form. You got "california", "CA", "Calif.", and " California " with a trailing space. Your VLOOKUP treats all four as different values.
Normalization is what makes them one value. It is also where most spreadsheet cleanup quietly fails.
The Old Way (PROPER, TRIM, CLEAN, SWITCH)
The standard stack looks like this.
=PROPER(TRIM(CLEAN(A1)))Two things go wrong immediately.
First, PROPER only knows one rule: capitalize after a non-letter. So "USA" becomes "Usa". "O'Connor" becomes "O'connor". "MacDonald" becomes "Macdonald". "IT Support" becomes "It Support". It mangles exactly the values you care most about getting right.
Second, TRIM only removes the regular space, CHAR(32). Data pasted from a web page is full of non-breaking spaces, CHAR(160). TRIM walks right past them. The cell looks clean, the lookup still fails, and you lose an afternoon. The fix is another layer of nesting.
=TRIM(SUBSTITUTE(CLEAN(A1), CHAR(160), " "))Then there is code mapping, where every variant needs its own branch.
=SWITCH(UPPER(TRIM(A1)), "CA", "California", "CALIF", "California", "NY", "New York", A1)That formula grows forever. Every new abbreviation someone invents means editing it again.
The FITS Way (Describe the Standard)
With FITS, you describe the target format once. The AI knows USA is an acronym and CA is California.
=FITS("Clean up " & A1 & ": fix capitalization, strip hidden and extra spaces, and keep acronyms and names like O'Connor correct")Standardizing codes needs no lookup table at all.
=FITS("Convert " & A1 & " to the full US state name. Handle abbreviations, misspellings, and extra spaces")One formula, no SWITCH branches. When a new variant shows up next quarter, you do not have to touch the sheet.
When to Use Each
TRIM and PROPER are free and instant when your data is simple and predictable. Reach for =FITS() when humans typed it, when it came from the web, or when acronyms and proper nouns matter. Go deeper with the definitive guide to AI data cleaning and our messy data extraction guide. Normalization is one of four big jobs that used to need a pattern. The rest are in automating Google Sheets tasks you used to need regex for.
Stop Letting PROPER Ruin Your Data
FITS puts plain-English AI formulas inside Google Sheets. Describe the standard. Get consistent cells. Free tier included.