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:
| A | B | |
| 1 | Name | John doe |
| 2 | JOHN@EXAMPLE.com | |
| 3 | State | ny |
| 4 | Address | 100 Example St |
Capitals letters in the wrong places and spaces everywhere.
Spreadsheets have some handy functions to help with these issues:
| A | B | |
| 1 | PROPER | Capitalizes Every Word |
| 2 | LOWER | lowercases every letter |
| 3 | UPPER | CAPITALIZES EVERY LETTER |
| 4 | TRIM | Removes leading, trailing, and unnecessary internal spaces |
Syntax
The syntax of all of these functions is the same:
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:
| A | B | C | |
| 1 | Original | Function | Output |
| 2 | John doe | =PROPER(A2) | John Doe |
| 3 | JOHN@EXAMPLE.com | =LOWER(A3) | john@example.com |
| 4 | ny | =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:
| A | B | C | |
| 1 | Name | jim HALPERT | |
| 2 | JAMES.HALPERT@DUNDERMIFFLIN.COM | ||
| 3 | State | pa | |
| 4 | Address | 13831 Calvert Street | |
| 5 | Occupation | paPer sAlesMan | |
| 6 |
FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:
You'll get updates from me with an easy-to-find "unsubscribe" link.