Summary  
This chapter introduces text-processing functions for trimming whitespace, normalizing case, substituting characters, extracting substrings, and formatting values to clean and standardize raw data.

General domain of usage  
data preprocessing

Real-world datasets often contain inconsistent formatting, extra spaces, and messy text values. In this chapter, clean and standardize customer data before using it in lookups and reporting.

Download the customer dataset and import it into Excel before starting the exercises.



Customers.csv

## TRIM and PROPER

```excel id="1"
=PROPER(TRIM(Customers!B2))
```

* `TRIM()`: removes extra spaces;
* `PROPER()`: fixes capitalization.

## Case Functions

```excel id="2"
=UPPER(TRIM(Customers!B2))
```





```excel id="3"
=LOWER(TRIM(Customers!B2))
```

* `UPPER()`: converts text to uppercase;
* `LOWER()`: converts text to lowercase.



## SUBSTITUTE Function

```excel id="4"
=SUBSTITUTE(Customers!C2, " ", "")
```

* `" "`: character being replaced;
* `""`: replacement value;
* Removes all spaces from the email value.



## Text Extraction Functions

```excel id="5"
=RIGHT(A2, 3)

=LEFT(A2, 2)

=MID(A2, 2, 3)
```

* `RIGHT()`: extracts text from the end;
* `LEFT()`: extracts text from the beginning;
* `MID()`: extracts text from the middle.



## TEXT Function

```excel id="8"
=TEXT(Customers!D2, "MMM YYYY")
```

Converts:

```text id="9"
15/03/2023 → Mar 2023
```




Go to:

```text id="10"
Data → Get Data → From Text/CSV
```

Preview the dataset before loading it into Excel.

Use:

```text id="11"
Use First Row as Headers
```

Load the dataset into a new worksheet.



Step 1 Import the Customers Dataset



Create a new worksheet named:

```text id="12"
Customers_Clean
```

Add the following columns:

```text id="13"
Customer_ID
Full_Name_Clean
```



Step 2 Create the Clean Table



Inside the first `Customer_ID` cell, type:

```excel id="14"
=Customers!A2
```

Fill the formula down the column.


Step 3 Copy Customer IDs



Inside the first `Full_Name_Clean` cell, type:

```excel id="15"
=PROPER(TRIM(Customers!B2))
```

Fill the formula down the column.



Step 4 Clean Customer Names



Inside a new column, type:

```excel id="16"
=SUBSTITUTE(Customers!C2, " ", "")
```

This removes invalid spaces from email addresses.



Step 5 Clean Email Values



Right-side extraction:

```excel id="17"
=RIGHT(A2, 3)
```

Middle extraction:

```excel id="18"
=MID(A2, 2, 3)
```


Step 6 Extract Customer ID Segments



Type:

```excel id="19"
=TEXT(Customers!D2, "MMM YYYY")
```

This standardizes the reporting format for dates.



Step 7 Format Dates for Reporting

Why is `TRIM` alone not enough to clean email addresses?

What is the purpose of the `TEXT` function in this lesson?

Build a complete Excel business analysis system using modern formulas, dynamic reporting, financial modeling, and scenario-based analysis. Throughout the course, you will transform raw transactional data into interactive dashboards, KPI engines, and scalable analytical models that reflect real-world business workflows.

Build the core Excel skills used in real business reporting workflows. Work with formulas, lookups, logical functions, conditional aggregation, error handling, and data cleaning while developing a structured sales analysis model.


Build dynamic Excel reporting systems using modern array functions and formula-driven workflows. Work with FILTER, SORT, SORTBY, UNIQUE, TRANSPOSE, spill ranges, interactive inputs, ranking systems, validation lists, and chart-ready data structures that update automatically as the dataset grows.


Build scalable relational Excel models using advanced lookup patterns and parameter-driven logic. Work with XLOOKUP, INDEX, MATCH, XMATCH, approximate matching, dynamic table relationships, scenario-based calculations, category-level aggregation, and flexible lookup architectures used in real analytical workflows.


Build advanced analytical and financial models that respond dynamically to changing data and assumptions. Work with time-based calculations, dynamic ranges, Named Ranges, installment modeling, investment analysis, amortization schedules, weighted aggregations, and scenario-driven financial metrics used in real business reporting systems.

Transform the workbook into a fully interactive business simulation system driven by scenarios, sensitivity analysis, and dynamic KPI logic. Build scenario engines, parameter-driven dashboards, sensitivity grids, advanced text-processing formulas, and centralized reporting layers that respond instantly to user inputs and business assumptions.

Preparing Clean Data

TRIM and PROPER

Case Functions

SUBSTITUTE Function

Text Extraction Functions

TEXT Function

1. Why is `TRIM` alone not enough to clean email addresses?

2. What does this formula do?

3. What is the purpose of the `TEXT` function in this lesson?