Home Courses Microsoft Excel Day 19: Find, Replace and Data Cleaning
WEEK 3 DAY 19 OF 30 60 MINUTES WORKBOOKS INCLUDED

Day 19: Find, Replace and Data Cleaning

Clean messy real-world datasets: master Find & Replace (Ctrl + H), =TRIM(), =PROPER(), and remove duplicates.

Easy-Learn Framework

Mastering Day 19: What, Why & How

Memory Rule Included
Day 19: Find, Replace and Data Cleaning Visual Diagram
? WHAT Is It?

Sanitizing messy raw data by stripping invisible spaces, correcting misspellings across sheets, and purging duplicate rows.

! WHY Does It Matter?

'Garbage in, garbage out' — if a customer city is typed as ' Haldwani ' with accidental spaces or 'Hldwn', VLOOKUP, pivot tables, and SUMIF formulas will fail. Clean data ensures trustworthy reports.

⚙ HOW Does It Work?
Step 1 Find and Replace: Press Ctrl + H to replace recurring misspellings or abbreviations across the entire sheet.
Step 2 Remove rogue spaces: Use =TRIM(cell) to strip accidental leading, trailing, and double spaces.
Step 3 Remove Duplicates: Select table → Data Tab → 'Remove Duplicates' to keep unique records only.
Memory Hook & Golden Rule (Remember This Forever):

"TRIM eliminates invisible space traps; Ctrl + H fixes repeated mistakes in 1 click; Remove Duplicates keeps databases pure."

Learning Objectives

What You Will Master Today

  • Find and Replace dialog options: Match case, Match entire cell contents
  • Eliminating invisible leading and trailing spaces with =TRIM(text)
  • Fixing mixed capitalization (e.g., 'rAhUl JoSHi') using =PROPER(text)
  • Data > Remove Duplicates tool to eliminate redundant customer records
Important Keyboard Shortcuts:
Ctrl + F (Find Dialog)Ctrl + H (Find and Replace)=TRIM() (Remove Extra Spaces)=PROPER() (Capitalize Words)
Guided Tutorial

Step-by-Step Practical Walkthrough

Step 1

Press Ctrl + H to open the Find and Replace dialog.

Step 2

In 'Find what', type the old string (e.g., 'Hld') and in 'Replace with', type 'Haldwani'.

Step 3

Click 'Replace All' to update hundreds of cells across the entire sheet simultaneously.

Step 4

Insert an adjacent helper column and type '=PROPER(TRIM(B5))' to sanitize messy names.

Solved Classroom Example

Haldwani City Hospital - Cleaned Patient Registration Records

Pre-filled Data & Answers

Study the completed reference worksheet below. Every column illustrates the standard formatting, formula structure, and alignment applied by professional office operators.

Patient IDRaw Messy NameCleaned Sanitized NameCity CodeStandardized CityMobile No
PAT-901 rAhUl jOsHi Rahul JoshiHLDHaldwani9876543210
PAT-902pooja BISHTPooja BishtRDPRudrapur9812345678
PAT-903DEEPAK rawat Deepak RawatHLDHaldwani9012398765
PAT-904 sneha aRyASneha AryaDDNDehradun9411122334
PAT-905amit negiAmit NegiNTLNainital9634567890
Download Solved Sample Sheet Includes complete working formula cells, proper currency symbols, and clean borders.
Download Solved Sheet (.xlsx)
Student Assignment

Raw E-Commerce Customer Address Directory (Uncleaned)

Your Turn to Practice
Exercise Tasks Checklist:
  • Task 1: In Column C, apply formula '=PROPER(TRIM(B5))' to strip leading/trailing spaces and format proper name casing.
  • Task 2: Copy Column C and Paste Special as Values (Alt + E + S + V) to lock cleaned text.
  • Task 3: Use Ctrl + H to find 'UK' and replace with 'Uttarakhand' across Column D.
  • Task 4: Check for duplicate rows using Data > Remove Duplicates on Customer ID.

Exercise Data Template Preview: Download the .xlsx file below and execute each task independently.

Customer IDRaw Full NameCleaned Name (=PROPER(TRIM()))State AbbreviationCleaned State
CUST-301 vikram mehra UK
CUST-302neha RAWATUK
CUST-303 karan arya UP
CUST-304kavita PANDEY UK
CUST-305gaurav TEWARIUK
Download Practice Exercise Sheet Pre-structured practice workbook with raw datasets ready for hands-on classroom lab solving.
Download Practice Sheet (.xlsx)
Mentor Pro Tip from Haldwani Classroom Lab:

Paste Special as Values is crucial after data cleaning! If you delete the raw column before pasting values, your formulas will return '#REF!' errors.

Day 18: Filtering Data... All 30 Topics Day 20: Excel Tables...

Need 1-on-1 Guidance on Day 19?

Join our hands-on classroom lab sessions at Digital Skills Academy in Haldwani. Every student receives a dedicated PC workstation, live instructor reviews, and interview prep.