Why this matters
Data rarely arrives clean. A survey export from a phone form, a list copied from WhatsApp, a bank statement downloaded as CSV: each brings extra spaces, mixed capitals, phone numbers missing their first zero and dates Excel cannot read. Data cleaning appears in MEL and MIS job adverts because reports built on dirty data are wrong. This lesson gives you a small toolkit that fixes the most common problems.
Work in helper columns
Never overwrite the original column straight away. Add a new column next to it, write a cleaning formula, fill it down, and check the results. When you are happy, copy the new column and use Paste Special, then Values to keep the clean text without the formula. Keep your raw copy as well.
Text functions
=TRIM(A2)removes spaces at the start and end and turns double spaces between words into single ones. " tamale central " becomes "tamale central".=PROPER(A2)capitalises each word: "tamale central" becomes "Tamale Central".=UPPER(A2)and=LOWER(A2)change everything to capitals or small letters.- You can combine them:
=PROPER(TRIM(A2)).