Module 3 · From ten thousand rows to one page Lesson 7 of 15
Cleaning an export nobody tidied
Real exports arrive with trailing spaces, duplicates, inconsistent capitals and dates stored as text. Cleaning is the first half of every analysis, not a chore before it.
The concept
An export from a portal arrives with merged headers, blank columns, dates as text, lakhs written as "1.2 L", and duplicate rows. Cleaning it is the first hour of every real task, and saying so in an interview is not an admission — it is what the job is.
Four moves clean almost anything. Remove blank rows and columns. TRIM and PROPER the text columns so " asha " and "ASHA" become "Asha". Split a combined column with "Text to Columns" or TEXTSPLIT. Then use "Remove Duplicates" on the id column, and count the rows before and after so you can report what you did.
=TRIM(A2) strip stray spaces
=PROPER(TRIM(A2)) Fix Title Case
=SUBSTITUTE(A2, " ", " ") collapse double spaces
=IF(A2 = "", "missing", A2) flag blanks
=LEFT(A2, FIND(" ", A2) - 1) first name
=TEXT(B2, "yyyy-mm-dd") force a date display
Data -> Remove Duplicates . Text to Columns . Filter -> sort A-ZCommon mistakes
The mistake is editing the raw export and losing the original. Keep a "raw" sheet you never touch, do all the work in a second sheet, and your numbers stay auditable — which is the difference between a report a manager trusts and one they re-check by hand.
Worked example
TRIM removes the extra spaces that make "North" and "North " count as two regions, Remove Duplicates drops the re-imported rows, and Text to Columns puts a mis-stored date back into a date.
Your practice task
Run all three on your own dataset, then write down how many rows you removed and what each removal was for — the note is what an interviewer asks about.
Do this before moving on. Reading a solution teaches the shape of it; typing it teaches you.
Key takeaway
An export from a portal arrives with merged headers, blank columns, dates as text, lakhs written as "1.2 L", and duplicate rows.
Keep your place in this course
A free account saves which lessons you have finished and unlocks the certificate. The lesson itself stays free to read.
Create free account or sign inCourse complete
You finished every lesson.
Your certificate is ready, and the projects you built are the proof to attach to an application.
Almost there
15 lessons left of 15
Finish every lesson to unlock the certificate. Each one ends in a practice task — do them and the final project brief writes itself.