Trimming Spaces & Fixing Case: TRIM, PROPER, UPPER
All lessons
- Trimming Spaces & Fixing Case: TRIM, PROPER, UPPERFree
- Splitting Codes & Names: LEFT, MID, FIND Pro
- Converting Text Numbers & Text Dates into Real Values Pro
- Standardising Categories with a Mapping Table (XLOOKUP) Pro
- Finding Duplicates & First Occurrences with COUNTIFS Pro
- Handling Blanks & Outliers: IF, MEDIAN and Flags Pro
- Validating Rows: Rule Checks with AND, OR and an Error Count Pro
- Capstone: an Analysis-Ready Table and a Region × Month Summary Pro
You are not signed in — the lab works, but your progress is not saved. Start free
Read first
Other quick fixes in Excel
- Find & Replace (Ctrl+H): replace two spaces with one, or remove a unit like " USD". Repeat until Excel finds nothing.
- Non-breaking spaces from web pages (character 160) survive TRIM. Remove them with
=TRIM(SUBSTITUTE(A2,CHAR(160)," ")). CLEAN removes line breaks and other non-printing characters. - Flash Fill (Ctrl+E): type the clean version of the first one or two rows, press Ctrl+E and Excel fills the pattern. Fast, but it does not update when the data changes. Formulas do.
- In Power Query (Data > From Table/Range): right-click a column > Transform > Trim, Clean or Capitalize Each Word. The steps are saved and run again on next month's export with one click on Refresh.
- When the clean columns are right: copy them, Paste Special > Values, then delete the raw columns (keep the raw file).
Loading the spreadsheet…