UPPER, LOWER, PROPER in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of =UPPER("Good Morning")?

You enter the formula =UPPER("Good Morning") in a cell. What will the cell display?

Google Sheets

=UPPER("Good Morning")

A good morning

B GOOD MORNING

C Good Morning

D GoOd MoRnInG

💡 Hint

UPPER converts all letters to uppercase.

The UPPER function changes all letters in the text to uppercase. So "Good Morning" becomes "GOOD MORNING".

📊 Formula Result

intermediate

What does =LOWER("HELLO WORLD") return?

You type =LOWER("HELLO WORLD") in a cell. What will be the result?

Google Sheets

=LOWER("HELLO WORLD")

A hello world

B HELLO WORLD

C Hello World

D hElLo WoRlD

💡 Hint

LOWER changes all letters to lowercase.

The LOWER function converts all letters to lowercase. So "HELLO WORLD" becomes "hello world".

📊 Formula Result

advanced

What is the output of =PROPER("john DOE smith")?

You enter =PROPER("john DOE smith") in a cell. What will the cell show?

Google Sheets

=PROPER("john DOE smith")

A John DOE Smith

B john doe smith

C JOHN DOE SMITH

D John Doe Smith

💡 Hint

PROPER capitalizes the first letter of each word and makes the rest lowercase.

The PROPER function capitalizes the first letter of each word and makes the rest lowercase. So "john DOE smith" becomes "John Doe Smith".

❓ Function Choice

advanced

Which formula converts "mArY aNnE" to "Mary Anne"?

You want to change the text "mArY aNnE" so that only the first letter of each word is uppercase and the rest are lowercase. Which formula should you use?

A =UPPER("mArY aNnE")

B =LOWER("mArY aNnE")

C =PROPER("mArY aNnE")

D =CONCATENATE("mArY aNnE")

💡 Hint

Think about which function capitalizes the first letter of each word.

PROPER capitalizes the first letter of each word and makes the rest lowercase. UPPER makes all uppercase, LOWER all lowercase, and CONCATENATE joins text without changing case.

🎯 Scenario

expert

You have a list of names in column A with mixed cases. You want to create a formula in column B that shows the names in all uppercase except the first letter of the entire name should be uppercase and the rest lowercase. Which formula will produce this result for cell A2?

Given cell A2 contains "jOhN doE", which formula in B2 will produce "John doe" (only the first letter of the entire text uppercase, rest lowercase)?

A =UPPER(LEFT(A2,1)) & LOWER(MID(A2,2,LEN(A2)-1))

B =LOWER(A2)

C =UPPER(A2)

D =PROPER(A2)

💡 Hint

Think about combining functions to capitalize only the first letter of the whole text.

PROPER capitalizes the first letter of each word, not just the first letter of the entire text. UPPER(LEFT(A2,1)) & LOWER(MID(A2,2,LEN(A2)-1)) capitalizes only the first letter and makes the rest lowercase.

Practice

1. What does the UPPER function do in Google Sheets?

easy

A. Reverses the text string.

B. Converts all letters in a text to lowercase.

C. Capitalizes only the first letter of the first word.

D. Converts all letters in a text to uppercase.

Solution

1. Step 1: Understand the UPPER function

   The UPPER function changes all letters in a text string to uppercase letters.

2. Step 2: Compare with other options

   LOWER changes letters to lowercase, PROPER capitalizes first letters of each word, and reversing text is unrelated.

3. Final Answer:

   Converts all letters in a text to uppercase. -> Option D

4. Quick Check:

   UPPER = all uppercase [OK]

Hint: UPPER = all caps, LOWER = all small, PROPER = first letters caps [OK]

Common Mistakes:

• Confusing UPPER with LOWER function
• Thinking PROPER only capitalizes first word
• Assuming UPPER reverses text

2. Which of the following is the correct syntax to convert cell A1 text to lowercase in Google Sheets?

easy

A. =LOWER A1

B. =LOWER(A1)

C. =LOWER(A1;

D. =LOWER{A1}

Solution

1. Step 1: Recall correct function syntax

   Functions require parentheses around arguments, so LOWER(A1) is correct.

2. Step 2: Check other options for syntax errors

   =LOWER A1 misses parentheses, C has a wrong closing bracket, D uses curly braces incorrectly.

3. Final Answer:

   =LOWER(A1) -> Option B

4. Quick Check:

   Correct syntax uses parentheses [OK]

Hint: Functions always use parentheses around arguments [OK]

Common Mistakes:

• Omitting parentheses
• Using wrong brackets or braces
• Adding extra characters like semicolons

3. Given cell A1 contains the text "hello world", what will the formula =PROPER(A1) return?

medium

A. "Hello World"

B. "HELLO WORLD"

C. "hello world"

D. "Hello world"

Solution

1. Step 1: Understand PROPER function behavior

   PROPER capitalizes the first letter of each word in the text.

2. Step 2: Apply PROPER to "hello world"

   Both "hello" and "world" get their first letters capitalized, resulting in "Hello World".

3. Final Answer:

   "Hello World" -> Option A

4. Quick Check:

   PROPER("hello world") = "Hello World" [OK]

Hint: PROPER capitalizes first letter of each word [OK]

Common Mistakes:

• Confusing PROPER with UPPER or LOWER
• Capitalizing only first word
• Leaving text unchanged

4. You want to convert the text in cell B2 to uppercase, but your formula =upper B2 returns an error. What is the fix?

medium

A. Use =UPPER[B2] with square brackets.

B. Add quotes around B2 like =UPPER("B2").

C. Change to =UPPER(B2) with uppercase function name.

D. Remove parentheses like =UPPER B2.

Solution

1. Step 1: Check function name case sensitivity

   Best practice is to use uppercase for function names like UPPER, and parentheses are required around cell references.

2. Step 2: Identify syntax error

   Options A-D: A uses invalid brackets, B treats B2 as literal text, C lacks parentheses.

3. Step 3: Correct formula syntax

   Using =UPPER(B2) is correct and standard.

4. Final Answer:

   Change to =UPPER(B2) with uppercase function name. -> Option C

5. Quick Check:

   Use =UPPER(cell) syntax [OK]

Hint: Use =UPPER(cell) with parentheses, no quotes or brackets [OK]

Common Mistakes:

• Adding quotes around cell references
• Using square brackets instead of parentheses
• Omitting parentheses

5. You have a list of names in column A with inconsistent capitalization, like "jAnE doE" and "mARk tWain". Which formula in cell B1 will correctly format the name in A1 to proper case, and how would you apply it to the whole column?

hard

A. In B1: =PROPER(A1), then drag the fill handle down to apply to all rows.

B. In B1: =UPPER(A1), then copy and paste values down.

C. In B1: =LOWER(A1), then drag down to apply.

D. In B1: =PROPER(A:A1) to apply to entire column at once.

Solution

1. Step 1: Choose correct function for capitalization

   PROPER capitalizes the first letter of each word, fixing inconsistent capitalization.

2. Step 2: Apply formula to first cell and extend

   Enter =PROPER(A1) in B1, then drag the fill handle down to copy the formula for all rows.

3. Step 3: Understand why other options fail

   UPPER makes all letters uppercase, LOWER all lowercase, and =PROPER(A1:A) is invalid syntax for array formula here.

4. Final Answer:

   In B1: =PROPER(A1), then drag the fill handle down. -> Option A

5. Quick Check:

   Use PROPER + drag fill for column [OK]

Hint: Use PROPER and drag down to fix capitalization in lists [OK]

Common Mistakes:

• Using UPPER or LOWER instead of PROPER
• Trying to apply PROPER to whole column without dragging
• Using incorrect array syntax