Lesson 2.5 – Basic Data Cleaning

Clean data is essential for accurate calculations, sorting, filtering, and analysis. Even small issues—extra spaces, inconsistent capitalization, or unwanted characters— can cause formulas to fail or produce incorrect results. In this lesson, you will learn simple but powerful tools to clean data quickly using Excel functions.

---

1. Why Data Cleaning Matters

Raw data often contains problems such as:

• Extra spaces before or after text
• Inconsistent capitalization
• Non-printable characters from imported files
• Mixed formats (text that looks like numbers)

Cleaning data ensures consistency and prevents errors in formulas and analysis.

---

2. TRIM – Remove Extra Spaces

TRIM(text) removes extra spaces from text, leaving only single spaces between words.

Example:

• Original: “ Product A ”
• Formula: =TRIM(A1)
• Result: “Product A”

TRIM is essential when working with imported or manually typed data.

---

3. CLEAN – Remove Non‑Printable Characters

CLEAN(text) removes hidden characters that often appear in data imported from PDFs, websites, or external systems.

Example:

• Original: “Product A□” (invisible character)
• Formula: =CLEAN(A1)
• Result: “Product A”

CLEAN is especially useful when data looks normal but behaves incorrectly.

---

4. PROPER – Capitalize Text Correctly

PROPER(text) converts text to “Title Case,” capitalizing the first letter of each word.

Example:

• Original: “john SMITH”
• Formula: =PROPER(A1)
• Result: “John Smith”

Use PROPER for names, titles, and labels.

---

5. Combining Cleaning Functions

You can combine TRIM, CLEAN, and PROPER to fix multiple issues at once.

Example:

=PROPER(TRIM(CLEAN(A1)))

This formula:

• removes non‑printable characters
• removes extra spaces
• applies proper capitalization

---

6. Converting Text Numbers to Real Numbers

Sometimes numbers are stored as text and cannot be used in formulas.

Fix:

• Use Value: =VALUE(A1)
• Or multiply by 1: =A1*1
• Or add zero: =A1+0

Excel then converts the text into a real numeric value.

---

7. Practical Exercise

Practice data cleaning with the following steps:

1. Create a worksheet named Lesson_2_5_Practice.
2. In column A, enter five messy text values (extra spaces, mixed case, symbols).
3. Use TRIM to remove extra spaces.
4. Use CLEAN to remove non‑printable characters.
5. Use PROPER to fix capitalization.
6. Combine all three functions in a single formula.
7. Convert text numbers into real numbers using VALUE.

---

Next Lesson

Module 3 – Essential Formulas and Functions