← VCE Spreadsheets, SQL & Visualisation

Cleaning text with TRIM, UPPER and PROPER

A text function that returns a tidy copy of the text in A2 with extra spaces removed. UPPER, LOWER and PROPER work the same way to fix capitals, e.g. =PROPER(A2) gives Mia Chen.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 28, VCE Data Analytics - U3O1 - Data Analytics — Module 74, VCE Data Analytics - U4O1 - Development and Evaluation — Module 5, 9-10 Digital Technologies: Acquiring, storing and validating data — Module 10, ACARA Digital Technologies Years 7–8 — Acquiring and Storing Data — Module 65, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 95, QCE Digital Solutions Unit 4 — Complex Digital Data Exchange Problems and Solution Requirements — Module 73

Understanding Cleaning text with TRIM, UPPER and PROPER in VCE Spreadsheets, SQL & Visualisation

What it does: LOWER converts all letters to lower case, which makes text consistent for sorting, filtering and comparing. TRIM removes extra spaces at the start and end and between words. PROPER only changes capitals, so the spaces would stay. Text functions return a new value in the cell holding the formula. The original data in A2 stays the same, which is why you often copy and paste the results as values. LOWER turns every letter into a small letter. UPPER does the opposite and makes every letter a capital. A spreadsheet function needs brackets around the cell reference, like =TRIM(C5). Without them the formula gives an error.

Common mistakes: TRIM needs its input inside round brackets. It should be =PROPER(TRIM(A2)). The spreadsheet function is called UPPER, not UPPERCASE. Using the wrong name gives a #NAME? error. It should be =UPPER(A2).

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Cleaning text with TRIM, UPPER and PROPER to a class →

This module's key terms are also in a printable crossword (Modules 21–34) and word search (Modules 4–34).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
Cell A2 contains " Mia Chen " with extra spaces. Which formula returns "Mia Chen"?
=PROPER(A2)=TRIM(A2)
Matches
=TRIM A2
↓
The cell reference is not inside brackets. A text function needs its input in brackets, so it should be =TRIM(A2).
Applying
Meanings
Term
=TRIM(A2)
Meaning
A text function that returns a tidy copy of the text in A2 with extra spaces removed. UPPER, LOWER and PROPER work the same way to fix capitals, e.g. =PROPER(A2) gives Mia Chen.
Practice
=PROPER(TRIM(A2))
Testing
Gaps
=(A2)
Check Answer
Spots
1A2: ' mia chen '
2B2: =PROPER(TRIM A2)

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Reef Shark' badge — Reef Ruler, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. Cell A2 contains " Mia Chen " with extra spaces. Which formula returns "Mia Chen"?
A. =TRIM(A2) ✓
B. =PROPER(A2)
TRIM removes extra spaces at the start and end and between words. PROPER only changes capitals, so the spaces would stay.
2. Cell A3 contains "JAMAL OKAFOR". Which formula returns "Jamal Okafor"?
A. =PROPER(A3) ✓
B. =TRIM(A3)
PROPER makes the first letter of each word a capital and the rest lowercase. TRIM only fixes spaces, so the text would stay in all capitals.
3. True or false: =UPPER(A2) changes the original text in cell A2.
A. True: it overwrites the text in A2
B. False: it returns a tidied copy in the cell with the formula ✓
Text functions return a new value in the cell holding the formula. The original data in A2 stays the same, which is why you often copy and paste the results as values.
4. You want every school code in column B to be lowercase, such as "vic" instead of "VIC". Which function should you use?
A. =UPPER(B2)
B. =LOWER(B2) ✓
LOWER turns every letter into a small letter. UPPER does the opposite and makes every letter a capital.
5. Which formula correctly cleans the text in cell C5?
A. =TRIM(C5) ✓
B. =TRIM C5
A spreadsheet function needs brackets around the cell reference, like =TRIM(C5). Without them the formula gives an error.

Match the Pairs

=TRIM(A2) → Returns a copy of the text in A2 with extra spaces removed from the start, the end and between words
=PROPER(A2) → Returns a copy of the text in A2 with the first letter of each word in capitals and the rest in lower case
Make every letter in the text in B2 lower case so that entries like MELBOURNE and melbourne match → =LOWER(B2)
Make every letter in the text in C2 a capital letter, such as turning vic into VIC → =UPPER(C2)
=TRIM A2 → The cell reference is not inside brackets. A text function needs its input in brackets, so it should be =TRIM(A2).
=PROPER(A2) typed in A2 → The formula is typed into the same cell it refers to, which makes a circular reference. Put it in another cell such as B2.

Key Term

=TRIM(A2)
A text function that returns a tidy copy of the text in A2 with extra spaces removed. UPPER, LOWER and PROPER work the same way to fix capitals, e.g. =PROPER(A2) gives Mia Chen.

Fill in the Blanks

1. =TRIM(A2)
Hint: Type the function that removes extra spaces from text.
2. =PROPER(A2)
Hint: Type the function that makes the first letter of each word a capital.
3. =UPPER(B2)
Hint: Type the function that changes every letter to a capital.
4. =LOWER(C2)
Hint: Type the function that changes every letter to small letters.
5. =PROPER(TRIM(A2))
Hint: Type the function nested inside that cleans up extra spaces first.

Spot the Error

Question 1
A A2: ' mia chen '
B B2: =PROPER(TRIM A2)
C B2 shows: Mia Chen
Line B — TRIM needs its input inside round brackets. It should be =PROPER(TRIM(A2)).
Question 2
A A2: 'sydney '
B B2: =UPPERCASE(A2)
C B2 shows: SYDNEY
Line B — The spreadsheet function is called UPPER, not UPPERCASE. Using the wrong name gives a #NAME? error. It should be =UPPER(A2).
Question 3
A A2: 'NSW '
B A3: 'vic'
C B2: =LOWER(TRIM(A2))
D B3: =LOWER(TRIM(A2))
Line D — B3 should clean the value in A3, but this formula points at A2 again. It should be =LOWER(TRIM(A3)).
Question 4
A A2: 'jAMES oDoNNell'
B B2: TRIM(A2)
C C2: =PROPER(B2)
Line B — A formula must start with an equals sign. Without it the spreadsheet treats this as plain text. It should be =TRIM(A2).
Question 5
A A2: ' tom lee'
B B2: =TRIM(A2)
C C2: =PROPER(B2)
D C2 shows: TOM LEE
Line D — PROPER gives only the first letter of each word a capital. TOM LEE would come from UPPER. C2 should show Tom Lee.

Student with a class code? Join your class →