Text, Date & Data Preparation
Learn how analysts clean, standardize and prepare messy business data before analysis. Practice Excel text functions, date functions and data-quality checks using a realistic customer dataset.
Why Data Preparation Matters
Real-world datasets are rarely perfect. Names may contain extra spaces, regions may use inconsistent capitalization, dates may need to be separated into useful components, and identifiers may contain unwanted characters.
If messy data is used directly for analysis, grouping, filtering and reporting can produce misleading results. Data preparation is therefore an important part of the analyst's workflow.
1. Start With a Messy Customer Dataset
Imagine that you receive the following customer data from different sales teams.
| Customer ID | Customer Name | Region | Signup Date | |
|---|---|---|---|---|
| C001 | Amit Sharma | north | 15/01/2025 | [email protected] |
| C002 | NEHA VERMA | South | 22/02/2025 | [email protected] |
| C003 | rahul singh | EAST | 10/03/2025 | [email protected] |
| C004 | Priya Joshi | west | 18/04/2025 | [email protected] |
| C005 | rohan mehta | NORTH | 05/05/2025 | [email protected] |
| C006 | simran kapoor | south | 21/06/2025 | [email protected] |
What Problems Can You See?
- Some names contain extra spaces.
- Names use inconsistent capitalization.
- Region values use different capitalization.
- Dates need to be analyzed as date dimensions.
2. Remove Unwanted Spaces with TRIM
Extra spaces can cause problems when comparing, matching or grouping text. The TRIM function removes unnecessary spaces from text.
Example
If B2 contains extra spaces around a customer's name, TRIM can create a cleaner version of the value.
FORMULA CHALLENGE
Write an Excel formula that removes unnecessary spaces from the customer name in B2.
The Excel function used to remove unnecessary spaces from text is ______.
3. Standardize Text with UPPER, LOWER and PROPER
Consistent capitalization makes datasets easier to read and standardize.
UPPER
Converts text to uppercase.
LOWER
Converts text to lowercase.
PROPER
Capitalizes the first letter of each word.
CHECK YOUR UNDERSTANDING
Which function would convert 'rahul singh' into 'Rahul Singh'?
CHECK YOUR UNDERSTANDING
Which function would convert 'North' into 'NORTH'?
4. Build a Cleaning Formula
Sometimes you need more than one cleaning operation. For example, you may want to remove spaces and standardize a customer's name.
Combining Functions
First TRIM removes unnecessary spaces. Then PROPER standardizes capitalization.
FORMULA CHALLENGE
Write a formula that removes extra spaces from B2 and then converts the cleaned name to proper capitalization.
To clean spaces first and then standardize capitalization, we can combine TRIM and ______.
5. Extract Parts of Text
Analysts often need only part of a text value. Excel provides functions such as LEFT, RIGHT and MID for extracting specific characters.
LEFT
Returns characters from the beginning of a text value.
RIGHT
Returns characters from the end of a text value.
MID
Extracts characters from a specified position.
CHECK YOUR UNDERSTANDING
Customer ID C001 begins with the characters 'C00'. Which function can extract the first three characters?
FORMULA CHALLENGE
Write a formula to extract the first three characters from the Customer ID in A2.
6. Extract Information From Email Addresses
Text extraction can also help analysts investigate identifiers and email addresses.
Example email:
A business may want to extract the domain portion for a separate analysis.
CHECK YOUR UNDERSTANDING
Which function is useful when you need characters from the end of a text value?
7. Work With Dates
Dates are extremely important in data analytics. Instead of looking only at a complete date, analysts often need the year, month or day to create useful dimensions for analysis.
YEAR
Returns the year from a date.
MONTH
Returns the month number from a date.
DAY
Returns the day number from a date.
FORMULA CHALLENGE
Write a formula to extract the year from the Signup Date in D2.
The Excel function used to extract the month number from a date is ______.
8. Turn Dates Into Analytical Dimensions
A complete signup date is useful, but creating separate Year and Month fields can make grouping and reporting easier.
CHECK YOUR UNDERSTANDING
If management wants to compare how many customers signed up in different months, which field should you create?
CHECK YOUR UNDERSTANDING
Why might an analyst create a separate Year field from Signup Date?
9. Data Quality Checks
Cleaning is not only about changing text. An analyst should also inspect the data for inconsistencies before using it for analysis.
Questions an analyst should ask
- Are customer names consistently formatted?
- Are region names standardized?
- Are dates valid and usable?
- Are customer IDs consistent?
- Are there unexpected blanks?
- Could extra spaces cause matching problems later?
CHECK YOUR UNDERSTANDING
Why should an analyst clean Region values such as 'north', 'North' and 'NORTH' before grouping the data?
10. Debug a Data-Cleaning Problem
Imagine you are trying to match customer names between two tables. One table contains "Amit Sharma" and another contains " Amit Sharma " with an extra space.
CHECK YOUR UNDERSTANDING
What is a useful first step when you suspect extra spaces are preventing a match?
CHECK YOUR UNDERSTANDING
A region analysis shows four separate categories: north, North, NORTH and North . What does this most likely indicate?
11. Think Like a Data Analyst
Data preparation should support the analysis you plan to perform. Before cleaning a dataset, think about which fields will be used for grouping, matching, filtering and reporting.
CHECK YOUR UNDERSTANDING
You plan to analyze customer signups by year and month. Which preparation step is most useful?
CHECK YOUR UNDERSTANDING
Why should data cleaning happen before important analysis?
Prepare a Customer Dataset for Reporting
A company is preparing a customer dashboard. The raw dataset contains inconsistent names, inconsistent region values and signup dates.
Before creating the dashboard, you need to prepare the data.
CHECK YOUR UNDERSTANDING
Which sequence is the most sensible starting workflow?
CHECK YOUR UNDERSTANDING
The dashboard needs customer signups by month. Which prepared field would be most useful?
Build a Reliable Data Preparation Workflow
You receive a customer dataset containing 50,000 records. Names contain inconsistent spaces and capitalization. Regions are inconsistent, and signup dates need to be analyzed by month.
What should you do before beginning the analysis?
CHECK YOUR UNDERSTANDING
Choose the strongest workflow.
Before performing important analysis, an analyst should clean and ______ the dataset.
Lesson 6 Key Takeaways
- Data preparation is an important part of the analytics workflow.
- TRIM can remove unnecessary spaces.
- UPPER, LOWER and PROPER help standardize text.
- LEFT, RIGHT and MID can extract parts of text values.
- YEAR, MONTH and DAY can turn dates into useful analytical dimensions.
- Inconsistent categories can create problems during grouping and reporting.
- Data should be validated after cleaning and before important analysis.