Cleaning text with TRIM, UPPER and PROPER
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Cell A2 contains " Mia Chen " with extra spaces. Which formula returns "Mia Chen"?
2. Cell A3 contains "JAMAL OKAFOR". Which formula returns "Jamal Okafor"?
3. True or false: =UPPER(A2) changes the original text in cell A2.
4. You want every school code in column B to be lowercase, such as "vic" instead of "VIC". Which function should you use?
5. Which formula correctly cleans the text in cell C5?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1=TRIM(A2) | AReturns a copy of the text in A2 with the first letter of each word in capitals and the rest in lower case | |
| 2=PROPER(A2) | BReturns a copy of the text in A2 with extra spaces removed from the start, the end and between words | |
| 3Make every letter in the text in B2 lower case so that entries like MELBOURNE and melbourne match | CThe cell reference is not inside brackets. A text function needs its input in brackets, so it should be =TRIM(A2). | |
| 4Make every letter in the text in C2 a capital letter, such as turning vic into VIC | DThe formula is typed into the same cell it refers to, which makes a circular reference. Put it in another cell such as B2. | |
| 5=TRIM A2 | E=UPPER(C2) | |
| 6=PROPER(A2) typed in A2 | F=LOWER(B2) |
C
Definitions
=TRIM(A2)
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=____(A2)
2.
=______(A2)
3.
=_____(B2)
4.
=_____(C2)
5.
=PROPER(____(A2))
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
A2: ' mia chen '
B
B2: =PROPER(TRIM A2)
C
B2 shows: Mia Chen
Question 2
A
A2: 'sydney '
B
B2: =UPPERCASE(A2)
C
B2 shows: SYDNEY
Question 3
A
A2: 'NSW '
B
A3: 'vic'
C
B2: =LOWER(TRIM(A2))
D
B3: =LOWER(TRIM(A2))
Question 4
A
A2: 'jAMES oDoNNell'
B
B2: TRIM(A2)
C
C2: =PROPER(B2)
Question 5
A
A2: ' tom lee'
B
B2: =TRIM(A2)
C
C2: =PROPER(B2)
D
C2 shows: TOM LEE
Answer Key
Cleaning text with TRIM, UPPER and PROPER
Section A: Quick Questions
Q1
A — =TRIM(A2)
Q2
A — =PROPER(A3)
Q3
B — False: it returns a tidied copy in the cell with the formula
Q4
B — =LOWER(B2)
Q5
A — =TRIM(C5)
Section B: Match the Pairs
Q1
=TRIM(A2) → Returns a copy of the text in A2 with extra spaces removed from the start, the end and between words
Q2
=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
Q3
Make every letter in the text in B2 lower case so that entries like MELBOURNE and melbourne match → =LOWER(B2)
Q4
Make every letter in the text in C2 a capital letter, such as turning vic into VIC → =UPPER(C2)
Q5
=TRIM A2 → The cell reference is not inside brackets. A text function needs its input in brackets, so it should be =TRIM(A2).
Q6
=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.
Section C: Definitions
Q1
=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.Section D: Fill in the Blanks
Q1
=TRIM(A2)Q2
=PROPER(A2)Q3
=UPPER(B2)Q4
=LOWER(C2)Q5
=PROPER(TRIM(A2))Section E: Spot the Error
Q1
B — TRIM needs its input inside round brackets. It should be =PROPER(TRIM(A2)).
Q2
B — The spreadsheet function is called UPPER, not UPPERCASE. Using the wrong name gives a #NAME? error. It should be =UPPER(A2).
Q3
D — B3 should clean the value in A3, but this formula points at A2 again. It should be =LOWER(TRIM(A3)).
Q4
B — A formula must start with an equals sign. Without it the spreadsheet treats this as plain text. It should be =TRIM(A2).
Q5
D — PROPER gives only the first letter of each word a capital. TOM LEE would come from UPPER. C2 should show Tom Lee.