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

Many students run Find & Replace directly on the original data, and if something goes wrong they can't go back. Always keep the Raw sheet untouched, work on a copy, and after cleaning reconcile the row count and the total amount. This habit will save you at work.