Ravindra BagaleCourses & study guides

16. Practice Exercises with Answer Hints

16.4 Module 5: Data Cleaning

By Last updated:

  1. Remove extra spaces and non-printable characters from customer names. Hint: =TRIM(CLEAN(A2)); for non-breaking spaces add SUBSTITUTE(A2,CHAR(160)," ").
  2. Standardise "pune", "PUNE ", " Pune" to "Pune". Hint: =PROPER(TRIM(A2)).
  3. Replace "Aurangabad" with "Sambhaji Nagar" in 400 rows safely. Hint: mapping table + XLOOKUP, or Home › Find & Select › Replace with Match entire cell contents.
  4. Convert "Rs. 1,299" to the number 1299. Hint: =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"Rs.",""),",","")).
  5. Convert text dates like 03/14/2026 (US style) to real dates. Hint: =DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2)) or Text to Columns › Date: MDY.
  6. Keep only the last 10 digits of phone numbers like "+91 90000 00021". Hint: =RIGHT(SUBSTITUTE(SUBSTITUTE(A2," ",""),"-",""),10).
  7. Split "Pune-411038" into City and Pincode. Hint: Text to Columns (delimiter -) or =TEXTBEFORE(A2,"-") / =TEXTAFTER(A2,"-") (Microsoft 365 / Excel 2024).
  8. Flag outliers in Amount using the IQR rule. Hint: Q1/Q3 with QUARTILE.INC; outlier if < Q1-1.5*IQR or > Q3+1.5*IQR.

Ravindra Bagale's Tip

Khup students original data var direct Find & Replace chalvtat aani chuk zali tar parat jaata yet nahi. Nehmi Raw sheet untouched theva, copy var kaam kara, aani cleaning nantar row count aani total amount reconcile kara. Hi savay job madhe tumhala vachvel.