LESSONS 1. Intro 2. Cell Referencing 3. Data Types 4. Operators 5. Functions 6. Intro To Logic 7. Simple Math 8. Math & Logic 9. Changing Text 10. Replacing Text 11. Splitting Text 12. Joining Text 13. Sorting Data 14. Rearranging Data 15. Looking Up Data

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 Email 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:

=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:

 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).

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.
 A B C 1 Name jim HALPERT 2 Email JAMES.HALPERT@DUNDERMIFFLIN.COM 3 State pa 4 Address 13831  Calvert        Street 5 Occupation paPer   sAlesMan 6
Next Lesson

FREE RESOURCE