Sign in or start a free trial to avail of this feature.
2. Change the Letter Case of Words
Using functions to change the letter case can be a big time-saver when cleaning text. In this lesson, we'll fix the formatting of our first name and last name text columns with this technique.
UPPER: Change cell text to uppercase
LOWER: Change cell text to lowercase
PROPER: Capitalises each word in a text string
ALT + I , C: Insert column
In this lesson, we're going to learn some simple, but very helpful functions that will allow us to change the letter case of words.
When you open a data set text entries are not often in the correct casing.
For this data set, which represents the signup details for a website, we can see that the first name is formatted as lowercase, and the last name as uppercase.
If we wanted to change the last name to lower case as well, we would simply pass the last name values into a function conveniently called "lower".
To show this in action, I'll create a new column, Alt IC and I'll write equals lower, open a bracket, and pass the last name value into this function.
When I close the bracket and press enter, you can see that the function converts rawlins to lowercase.
To implement this for the remaining entries we'll simply select the rawlins cell and double click the mouse in the bottom right hand corner.
And now we have all of our last name entries in lowercase.
If instead we wanted to store both the first name and last name as uppercase, you can probably guess that we use a function called "upper".
So again I'll create a new column with Alt IC, and here I’ll write a function equals upper, open the bracket and pass the first name value into this function.
When I close the bracket and press enter, you can see that this value has been converted to uppercase.
And again, if I want to auto fill for the remaining entries, I'll double click the mouse in the bottom right hand corner.
While upper and lower can be useful functions, perhaps the most popular case related function is "proper", which capitalizes the first letter of each word in a text string.
To see it in action, let's create a new column again, with Alt IC, and here I'll pass in the first name value to a function called proper.
And when I close the bracket and press enter, you can see that there's a capital K for Kaci, and the remaining letters are lower case.
For large data sets that need simple case changes like this, upper, lower and proper can be a huge time saver when the alternative for some users would be to manually change each entry.
These functions can also be used with other text functions very effectively, which I'll show in the next lesson when we combine multiple strings of text together.