Webbo3 Data Analytics · Cohort 2 · Excel Track · Week 2, Live 2

Cleaning Text With TRIM, PROPER, UPPER, LOWER, CLEAN, FIND and SUBSTITUTE

Last class, LEN proved the spaces were there. Today you remove them, fix the capital letters, strip out invisible characters, and find and replace pieces of text: Steps 6 and 7 of the Cleaning Process.

Chidi Okonkwo works at Lagos Island. In the Sales Data sheet his name is typed 9 different ways: CHIDI OKONKWO, chidi okonkwo, and the same with spaces before or after. To Excel those are different people. Today's functions make every one of them say exactly what it means.

Start from Sales Data after Steps 3 to 5 (duplicates removed, Unknown filled in). One rule for the whole class: a cleaning function never changes the original cell. It writes a cleaned copy in a new column. You check the copy, and only then replace the original (Step 8, in Virtual 2).

1. The Cleaning Functions

Definition

Five functions each fix one kind of text problem. Each takes one cell and returns a cleaned copy of its text.

Real-Life Scenario

Name tags at a wedding, written by ten different ushers. One wrote in capitals, one in small letters, one left a big gap before the name, one wrote on a crumpled card. Before the tags go on the seating chart, somebody rewrites them neatly, the same way. These functions are that somebody.

The Five Functions

Function 1: TRIM (Step 6)

TRIM removes every space at the start and end of the text, and squeezes any run of spaces in the middle down to one. It leaves single spaces between words alone, so "Chidi Okonkwo" keeps its space.

The red dots are gone. But look at the capital letters: "CHIDI OKONKWO" and "chidi okonkwo" are still different from "Chidi Okonkwo". TRIM does one job only.

Every version is now 13 characters, the length of "Chidi Okonkwo". LEN is how you prove a clean, not just how you hope for one.

Function 2: PROPER

The capitals are fixed, but the red dots are back: PROPER does not touch spaces. You need both functions, and section 2 puts them together.

12 of the 20 names come out wrong: iPhone becomes Iphone, HP becomes Hp, TV becomes Tv, TP-Link WiFi becomes Tp-Link Wifi. The product names were already right. That is why the Cleaning Process tab names exactly four columns to clean (Staff_Name, Branch, Category, Customer_Type) and never says "the whole sheet". Use PROPER only on a column that needs it.

Function 3: UPPER

Every value is in capitals now, but the spaces are still there. UPPER is useful when a system expects capitals (codes, labels, IDs) or when you want two spellings to compare equal regardless of case.

Function 4: LOWER

LOWER is the mirror of UPPER. You will use it less in reports, but it is the standard choice for email addresses and usernames, which systems usually expect in small letters.

Function 5: CLEAN

Some characters cannot be seen at all. Copy a name out of a WhatsApp message or an email and you may bring a line break or a tab with it. CLEAN removes those non-printing characters. To show it, here are three branch names pasted from a message, with the invisible characters drawn in red: a line break (↵), a tab (→) and both.

2. Combining Text Functions (Step 7)

Definition

You can put one function inside another, so the result of the inner one becomes the input of the outer one. This is called nesting. Excel always works from the inside out.

Real-Life Scenario

Washing clothes, then ironing them. The iron works on whatever comes out of the washing machine. You write the instruction as "iron (wash (shirt))", and it happens wash first, iron second.

How Excel Reads It

In =PROPER(TRIM(I2)), Excel first works out TRIM(I2), which removes the spaces, then hands that result to PROPER, which fixes the capitals. One formula, two fixes. The brackets must match: every opening bracket needs a closing one, and the formula bar colours each pair while you type to help you.

Every version of Chidi's name is now Chidi Okonkwo. Filter Staff_Clean and you will see exactly 10 names, the ten people in the Staff Directory. The order of the two functions does not matter here; =TRIM(PROPER(I2)) gives the same result, because neither undoes the other.

The Same Fix on the Other Columns

Step 9 of the Cleaning Process repeats this on Branch, Category and Customer_Type. Category is the interesting one: its only problem is capitals, so TRIM finds nothing to do, and PROPER does all the work:

Five categories, exactly as the Cleaning Process tab promises in Step 10: Accessories, Appliances, Laptops, Smartphones, Televisions.

3. FIND

Definition

FIND(what, text) returns the position of the first place what appears inside text, counting from 1. It is how you stop guessing positions for LEFT and MID.

Real-Life Scenario

A list where each line is "Firstname Surname". To split it you do not count letters: you look for the space, and everything before it is the first name. FIND looks for the space for you.

Example 1: Where Is the First Space?

In "Samsung Galaxy A54" the space is character 8, so the first word is the 7 characters before it. In "HP Laptop 15" it is character 3. The position changes; FIND finds it each time.

Example 2: FIND Inside LEFT, the First Word of Every Product

Combine it with LEFT. We want everything before the space, which is the space's position minus 1 characters:

For most products the first word is the brand. Not for all: compare with the Brand column of the Product Catalogue tab, and 1 disagree, such as iPhone 14, whose brand is Apple. A formula that cuts text can only find what is written; it cannot know what is meant. In Week 4 you will fetch the real brand from the catalogue with XLOOKUP instead.

When FIND Finds Nothing

Most rows show #VALUE!: Excel's way of saying "not found". Only TP-Link WiFi Router has a hyphen, at position 3. You will learn to catch errors like this neatly with IFERROR in Week 4.

4. SUBSTITUTE

Definition

SUBSTITUTE(text, old, new) replaces every copy of old inside the text with new. To delete something, replace it with nothing: two double quotes with nothing between them, "".

Real-Life Scenario

Find and Replace in a document, but in a formula, so it works on every row at once and leaves the original untouched.

Example 1: Removing a Character

Suppose TechPlug moves to a new till system that rejects hyphens in transaction numbers.

Example 2: Replacing an Awkward Character

Four TV names use the inch mark, a double quote: Samsung 43" TV. That character causes trouble in CSV files and text messages. You cannot simply type it inside a formula's quotes, so Excel gives it a code: CHAR(34) is the double quote character.

"Samsung 43" TV" becomes "Samsung 43 inch TV". Every name without an inch mark comes back unchanged. No errors, no damage: SUBSTITUTE is safe to run on a whole column.

And the fix from the CLEAN note: =TRIM(SUBSTITUTE(I2,CHAR(160)," ")) turns every non-breaking space into an ordinary space, then TRIM tidies up. CHAR(160) is the code for that invisible character.

Overall Example: A First Name for Every Sale

One new symbol first. The ampersand, &, joins pieces of text into one: ="Well done, "&"Chidi" gives Well done, Chidi. TechPlug wants a thank-you line for each salesperson, using only their first name:

Read it from the inside out. FIND finds the space in the cleaned name, LEFT takes everything before it, and the ampersands join it to the words around it. It only works because J was trimmed first: on " chidi okonkwo" the first space is at position 1, and LEFT would hand back nothing.

Practical Exercises

Exercise 1: Very Easy

Use TRIM on the Branch column and check with LEN that "Kano" is 4 characters everywhere.

Exercise 2: Simple Application

Use =PROPER(TRIM(K2)) to clean Customer_Type (after your inserted column). Filter the result: how many different values do you see?

Exercise 3: Combination

Use FIND and MID to pull the second word of each staff name (the surname). Hint: start one character after the space.

Exercise 4: A Small Real-World Problem

A colleague cleaned Product_Name with PROPER "to be safe". Write two sentences for your pod chat explaining what went wrong and how to undo it.

Checkpoint

Next, Week 2, Virtual 2: Steps 8 to 10 on your own: paste the clean names back, repeat for Branch, Category and Customer_Type, and check your work. Then you open the Verdant Foods file.