Column transformations in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to convert the text in cell A1 to uppercase.

Excel

=UPPER([1])

Drag options to blanks, or click blank then click option'

AA1

BB1

Ca1

D1A

Attempts:

3 left

💡 Hint

Common Mistakes

Using lowercase cell references like 'a1' which Excel recognizes properly.

Referencing the wrong cell like B1 or 1A.

The UPPER function converts text to uppercase. You need to reference the cell A1 where the text is located.

2fill in blank

medium

Complete the formula to extract the first 5 characters from the text in cell B2.

Excel

=LEFT([1], 5)

Drag options to blanks, or click blank then click option'

AA2

BC2

CB2

DD2

Attempts:

3 left

💡 Hint

Common Mistakes

Referencing the wrong cell like A2 or C2.

Using lowercase cell references.

The LEFT function extracts characters from the start of a text string. You need to reference cell B2 where the text is.

3fill in blank

hard

Fix the error in the formula to replace all spaces in cell C3 with underscores.

Excel

=SUBSTITUTE([1], " ", "_")

Drag options to blanks, or click blank then click option'

AC3

Bc3

C"C3"

DC4

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the cell reference in quotes, which makes it a text string.

Using lowercase cell references which Excel recognizes properly.

The SUBSTITUTE function replaces text within a string. The cell reference should be C3 without quotes.

4fill in blank

hard

Fill both blanks to create a formula that trims extra spaces from cell D4 and converts the result to proper case.

Excel

=PROPER([1]([2]))

Drag options to blanks, or click blank then click option'

ATRIM

BD4

CUPPER

DLOWER

Attempts:

3 left

💡 Hint

Common Mistakes

Using UPPER or LOWER instead of TRIM inside the formula.

Referencing the wrong cell.

The TRIM function removes extra spaces, and PROPER capitalizes the first letter of each word. The cell reference is D4.

5fill in blank

hard

Fill all three blanks to create a formula that concatenates the text in cells E1 and F1 with a space between them.

Excel

=CONCATENATE([1], [2], [3])

Drag options to blanks, or click blank then click option'

AE1

B" "

CF1

DE2

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to put the space in quotes.

Referencing the wrong cells.

Using cell E2 instead of E1.

The CONCATENATE function joins text. You need to join E1, a space " ", and F1 to get the combined text with a space.

Practice

1. What does the formula =A2 & " " & B2 do when applied in a new column?

easy

A. Adds the numbers in cells A2 and B2

B. Combines the text from cells A2 and B2 with a space between

C. Subtracts the value in B2 from A2

D. Multiplies the values in A2 and B2

Solution

1. Step 1: Understand the & operator in Excel

   The & operator joins (concatenates) text strings together.

2. Step 2: Analyze the formula =A2 & " " & B2

   This formula joins the content of A2, a space " ", and the content of B2 into one text string.

3. Final Answer:

   Combines the text from cells A2 and B2 with a space between -> Option B

4. Quick Check:

   Concatenate text with & = combined text [OK]

Hint: Use & to join text with spaces easily [OK]

Common Mistakes:

• Thinking & adds numbers instead of joining text
• Forgetting to add space between texts
• Using + instead of & for text

2. Which of these formulas correctly converts the text in cell A2 to uppercase in a new column?

easy

A. =UPPER(A2)

B. =PROPER(A2)

C. =LOWER(A2)

D. =TEXT(A2, "UPPER")

Solution

1. Step 1: Identify the function to convert text to uppercase

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

2. Step 2: Check each option

   LOWER() makes text lowercase, PROPER() capitalizes first letters, TEXT() formats numbers, so only UPPER(A2) is correct.

3. Final Answer:

   =UPPER(A2) -> Option A

4. Quick Check:

   UPPER() = uppercase text [OK]

Hint: Use UPPER() to change text to all caps [OK]

Common Mistakes:

• Using LOWER() instead of UPPER()
• Confusing PROPER() with uppercase
• Trying to use TEXT() for text case

3. Given the data in columns A and B: A2=10, B2=5, what is the result of the formula =A2*B2+2 in a new column?

medium

A. 52

B. 50

C. 12

D. 15

Solution

1. Step 1: Calculate the multiplication part

   Multiply A2 (10) by B2 (5) to get 50.

2. Step 2: Add 2 to the product

   Add 2 to 50, resulting in 52.

3. Final Answer:

   52 -> Option A

4. Quick Check:

   10*5+2 = 52 [OK]

Hint: Multiply first, then add according to order of operations [OK]

Common Mistakes:

• Adding before multiplying (wrong order)
• Confusing cell references
• Ignoring the +2 part

4. You want to create a new column that shows the full name by combining first name in A2 and last name in B2 with a space. Which formula has an error?

medium

A. =A2 & " " & B2

B. =CONCATENATE(A2, " ", B2)

C. =A2 + " " + B2

D. =TEXTJOIN(" ", TRUE, A2, B2)

Solution

1. Step 1: Understand text joining in Excel

   Text strings are joined with & or CONCATENATE or TEXTJOIN functions, not with +.

2. Step 2: Identify the incorrect formula

   Formula D uses + to join text, which causes an error because + is for numbers.

3. Final Answer:

   =A2 + " " + B2 -> Option C

4. Quick Check:

   Use & or CONCATENATE for text, not + [OK]

Hint: Use & or CONCATENATE, never + for text join [OK]

Common Mistakes:

• Using + to join text strings
• Forgetting quotes around space
• Mixing number and text operators

5. You have a column with dates in A2:A10. You want a new column that shows the month name (like "January") for each date. Which formula should you use in B2 and drag down?

hard

A. =MONTH(A2)

B. =YEAR(A2)

C. =DAY(A2)

D. =TEXT(A2, "mmmm")

Solution

1. Step 1: Understand the goal

   You want the full month name, not just the number.

2. Step 2: Check each formula's output

   MONTH() returns month number, DAY() returns day number, YEAR() returns year number. TEXT(A2, "mmmm") converts date to full month name text.

3. Final Answer:

   =TEXT(A2, "mmmm") -> Option D

4. Quick Check:

   TEXT(date, "mmmm") = month name [OK]

Hint: Use TEXT(date, "mmmm") for full month name [OK]

Common Mistakes:

• Using MONTH() which returns number, not name
• Confusing DAY() or YEAR() with month
• Not using quotes correctly in TEXT format