Project: Customer & Employee Data Cleaning System
Create a worksheet with the following raw data:
|
Customer Name |
|
Customer Code |
Phone |
City |
|
amit kumar |
AMIT@GMAIL.COM |
CUST-2026-001 |
9876543210 |
sirsa |
|
neha sharma |
NEHA@GMAIL.COM |
CUST-2026-002 |
9876543211 |
HISAR |
|
rahul singh |
RAHUL@GMAIL.COM |
CUST-2026-003 |
9876543212 |
sirsa |
|
PRIYA GUPTA |
PRIYA@GMAIL.COM |
CUST-2026-004 |
9876543213 |
FATEHABAD |
Required Cleaning
Clean Name
=PROPER(TRIM(A2))
Clean Email
=LOWER(TRIM(B2))
Extract Customer Prefix
=LEFT(C2,4)
Extract Year
=MID(C2,6,4)
Extract Customer Number
=RIGHT(C2,3)
Find @ Position
=FIND(“@”,B2)
Clean City
=PROPER(TRIM(E2))
Create a Final Clean Table
|
Customer Name |
|
Prefix |
Year |
Number |
City |
|
Amit Kumar |
CUST |
2026 |
001 |
Sirsa |
|
|
Neha Sharma |
CUST |
2026 |
002 |
Hisar |
|
|
Rahul Singh |
CUST |
2026 |
003 |
Sirsa |
|
|
Priya Gupta |
CUST |
2026 |
004 |
Fatehabad |
Final Challenge
Create a Customer Data Cleaning Report that uses at least 10 different text functions and produces a clean, professional dataset from raw data.