Trim, Lower, and Upper
Trim, Lower, and Upper
Formula: Trim(), Lower(), and Upper()
Category: String
Trim()Trims extra spaces before and after text
It’s kind of like saying...
Lower()Converts your text values to lower case
Or put another way...
Upper()Converts your text values to lower case
Or put another way...
All three formulas use the same, single parameter.
Text
Trim(Text)
This is the text you want to trim or convert to lower/upper case.
How Do They Work?
Trim(), Lower() , and Upper() are simple formulas to understand, so this section will be brief. But don’t skip the activities! These formulas are an absolute!
Original Sentence
Woah! Watch out for the rogue asteroid. It may take down your ship. This sentence is characters long
75
Trim()
Lower()
Upper()
Modified Sentence:
[ ]This sentence is characters long
0
Why Are They Important?
As an exercise, look at the tables below. Why do you think the Total Votes column below in yellow only has a total of 4 votes? Click into the column to view its formula, or see it to the right.
6 People Voted
4 total votes have been countedClass choices for favorite animal
1
2
3
4
5
6
Animal
Dog
Dog
Cat
Cat
Dog
Cat
There are no rows in this table
Final tally of votes
1
2
Image
Name
Total Votes
The Wrong Way
Dog
2
3
Cat
2
3
There are no rows in this table
From first glance, the formula is sound! It should work. But double click into the first two dog columns (the columns in blue) and pay attention to where your cursor lands.
If you pay attention closely you will notice that the first Dog value is only three characters long, but the 2nd Dog value is actually 4 characters long and includes an extra space at the end. These spaces will actually mess up your filter formulas. This is just one way to validate your data and ensure your filters work!
How to fix your filters: The wrong way
Filters (Filter()) are the number one culprit when it comes to a decrease in doc-performance.
Click into the wrong way column below to see how you should not write your formulas
1
2
Image
Name
The Wrong Way
Dog
3
Cat
3
There are no rows in this table
Writing calculations or formulas inside your Filter() formulas is the wrong way - as much as you can, try to avoid it at all costs. Always compare values to values.
Now that you have learned the wrong way, go ahead and look into the right way.
How to fix your filters: The right way
Instead of using Trim() and Lower() inside of the filter formula itself, it is much faster (for your docs performance) to create secondary helper columns that trim/lower the values and then compare those columns within your filter formula
See the Animal Trimmed column below, and finally, click into the green Right Way column below to see how the formula is written.
1
2
3
4
5
6
Animal
Animal Helper
Dog
dog
Dog
dog
Cat
cat
Cat
cat
Dog
dog
Cat
cat
There are no rows in this table
1
2
Image
Name
Animal Helper
The Right Way
Dog
dog
3
Cat
cat
3
There are no rows in this table
Practice: Practice using your three formulas
Done
Count the number of names in a table that are the same
Done
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.