Text, Date & Data Preparation
Learn how analysts clean, standardize and prepare messy business data before analysis using Excel text functions, date functions and data-quality checks.
Data Preparation
Practice Dataset
Messy Customer Dataset
The dataset from the lesson contains inconsistent names, regions and dates that need preparation before analysis.
| Customer ID | Customer Name | Region | Signup Date | |
|---|---|---|---|---|
| C001 | Amit Sharma | north | 15/01/2025 | amit@example.com |
| C002 | NEHA VERMA | South | 22/02/2025 | neha@example.com |
| C003 | rahul singh | EAST | 10/03/2025 | rahul@example.com |
| C004 | Priya Joshi | west | 18/04/2025 | priya@example.com |
| C005 | rohan mehta | NORTH | 05/05/2025 | rohan@example.com |
| C006 | simran kapoor | south | 21/06/2025 | simran@example.com |
Concept 1 • DATA QUALITY
See the Messy Dataset First
Real-world datasets are rarely perfect. Customer names may contain extra spaces, capitalization may be inconsistent, regions may appear as north, North and NORTH, and dates may need to become useful analytical dimensions.
What is wrong with the raw data?
Interactive Activity
Spot the problem
Which data-quality issue is clearly visible in the customer dataset?
Analyst Workflow
Prepare the data before trusting the analysis
Data preparation should support the analysis you plan to perform. Think about which fields will be used for grouping, matching, filtering and reporting.
Inspect
Find inconsistencies
Clean
Remove unwanted spaces
Standardize
Make categories consistent
Prepare
Create date dimensions
Validate
Check before analysis
Final Analyst Challenge
Prepare 50,000 customer records
You receive a customer dataset containing inconsistent spaces and capitalization in names, inconsistent regions, and signup dates that need to be analyzed by month.
Text, Date and Data Preparation in Excel
Excel data preparation is an important part of data analytics. Real-world datasets can contain extra spaces, inconsistent capitalization, inconsistent categories and dates that need to be converted into useful analytical fields.
Excel Text Functions for Data Cleaning
TRIM can remove unnecessary spaces, while UPPER, LOWER and PROPER can help standardize text. LEFT, RIGHT and MID can extract useful parts of identifiers and other text values.
Excel Date Functions for Data Analytics
YEAR, MONTH and DAY can extract components from dates. Separate date dimensions can make grouping and reporting easier when analyzing customer signups or other time-based data.
Why Data Quality Matters
Cleaning is not simply formatting. Analysts should inspect data quality, standardize equivalent values, prepare fields required for analysis and validate the prepared dataset before important reporting.