Text data can be ugly.

It might have been entered in strange ways by people or when copying and pasting things get a little weird.

For example, this data is a mess:

AB
1NameJohn doe
2EmailJOHN@EXAMPLE.com
3Stateny
4Address    100        Example   St  

Capitals letters in the wrong places and spaces everywhere.

Spreadsheets have some handy functions to help with these issues:

AB
1PROPERCapitalizes Every Word
2LOWERlowercases every letter
3UPPERCAPITALIZES EVERY LETTER
4TRIMRemoves leading, trailing, and unnecessary internal spaces

Syntax

The syntax of all of these functions is the same:

=FUNCTION(text)

Simply type the function name and enter the text or a cell reference to the text you want to change.

Example

Let’s see what these function can do to fix the messy data from earlier:

ABC
1OriginalFunctionOutput
2John doe=PROPER(A2)John Doe
3JOHN@EXAMPLE.com=LOWER(A3)john@example.com
4ny=UPPER(A4)NY
5    100        Example   St  =TRIM(A5)100 Example Street

Earlier in the course you learned about nesting functions. You can do this with text functions too:

If you write a formula that says =TRIM(PROPER(A2)) it will first capitalize every word (because this is the innermost function) AND then remove the leading, trailing, and unnecessary internal spaces (as this is the outermost function).

Now it’s your turn:

Changing Text Exercises
Enter a formula for each question in the formula bar for the spreadsheet below to access the next lesson.
1. PROPER
In cell C1, enter a formula that capitlizes Jim's name properly.
2. LOWER
In cell C2, enter a formula that makes Jim's email lowercase.
3. UPPER
In cell C3, enter a formula that makes the state abbreviation all uppercase.
4. TRIM
In cell C4, enter a formula that trims all of the extra spaces from the address.
5. Combination
In cell C5, enter a formula that combines two functions to trim the extra spaces and capitalize the first letter of each word of Jim's occupation. Remember that you can 'nest' one function inside another.
ABC
1Namejim HALPERT
2EmailJAMES.HALPERT@DUNDERMIFFLIN.COM
3Statepa
4Address   13831  Calvert        Street   
5Occupation  paPer   sAlesMan
6
Next Lesson

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.