Spreadsheet data cleaning: spaces, text numbers, dates and duplicates
For office and operations staff who receive exports from other systems and have to make them usable in Excel. Prerequisite: spreadsheet fundamentals (tables, simple formulas, sorting). You learn a repeatable cleaning method: keep the original, profile the data, remove spaces and hidden characters, convert numbers and dates stored as text, split columns, and remove duplicates without losing records. Based on Microsoft's Excel documentation; other spreadsheet apps work similarly but check their own help.
- Level
- Intermediate
- Length
- About 80 minutes
- Contents
- 4 lessons · final exam
- Status
- Published · updated 10 Oct 2026
Skills you'll practise
- Identify spaces, non-printing characters, numbers stored as text and text dates using helper formulas and Excel's visual signs
- Choose between TRIM, CLEAN and SUBSTITUTE for a given character problem
- Convert numbers and dates stored as text to real values without changing their meaning
- Split a column with Text to Columns without overwriting neighbouring data, and remove duplicates on the right key columns
Course outline
- 1.Before you clean: identify problems with helper formulas and visual signsLesson · 12 min
- 2.Choose TRIM, CLEAN or SUBSTITUTE for each character problemLesson · 16 min
- 3.Convert numbers and dates stored as text to real valuesLesson · 18 min
- 4.Split a column with Text to Columns, then remove duplicatesLesson · 16 min
- 5.Spreadsheet data cleaning: spaces, text numbers, dates and duplicates: knowledge checkKnowledge check · 14 questions
- 6.Spreadsheet data cleaning: spaces, text numbers, dates and duplicates: practical exerciseKnowledge check · 1 question
- 7.Final exam8 questions · passing it completes the course, so people who already know the material can test out
Sources it draws on
The lessons and questions are written from these references, so learners can go back to the original.
- Microsoft Support: Top ten ways to clean your data
- Microsoft Support: TRIM function (removes ASCII space 32, not nonbreaking space 160)
- Microsoft Support: CLEAN function (removes characters 0 to 31)
- Microsoft Support: Convert numbers stored as text to numbers in Excel
- Microsoft Support: VALUE function
- Microsoft Support: Convert dates stored as text to dates
- Microsoft Support: Change the date system, format, or two-digit year interpretation
- Microsoft Support: Split text into different columns with the Convert Text to Columns Wizard
- Microsoft Support: Filter for unique values or remove duplicate values
See it with your own jobs and topics
Tell us about your team and we'll walk you through setup, from choosing jobs to your first skills check.