LEFT, RIGHT, MID in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Extracting a substring with LEFT function

Given cell A1 contains the text "Spreadsheet", what is the result of the formula =LEFT(A1, 5)?

A "Sheet"

B "Sprea"

C "aerpS"

D "Spread"

💡 Hint

LEFT extracts characters starting from the beginning of the text.

LEFT(A1, 5) takes the first 5 characters from the left of "Spreadsheet", which are "Sprea".

📊 Formula Result

intermediate

Using RIGHT to get last characters

If cell B2 contains "DataAnalysis", what does =RIGHT(B2, 4) return?

A "sisy"

B ysis"

C "ysis"

D "ysis

💡 Hint

RIGHT extracts characters from the end of the text.

RIGHT(B2, 4) extracts the last 4 characters of "DataAnalysis", which are "ysis".

📊 Formula Result

advanced

Extracting middle text with MID

Cell C3 contains "Information". What is the output of =MID(C3, 4, 5)?

A "tamro"

B "rmat"

C "tamr"

D "ormat"

💡 Hint

MID extracts characters starting at a position for a given length.

MID(C3, 4, 5) starts at the 4th character of "Information" (which is 'o') and extracts 5 characters: "ormat".

🎯 Scenario

advanced

Combining LEFT and RIGHT to extract parts

You have a cell D4 with the text "Report2024Final". Which formula extracts the year "2024" from this text?

A =MID(D4, 7, 4)

B =RIGHT(D4, 4)

C =MID(D4, 8, 4)

D =LEFT(D4, 4)

💡 Hint

Count characters to find where the year starts.

The year "2024" starts at the 7th character in "Report2024Final". MID(D4, 7, 4) extracts 4 characters starting at position 7.

❓ Function Choice

expert

Choosing the correct function for dynamic substring extraction

You want to extract the last 3 characters from a text in cell E5, but the text length varies. Which formula will always return the last 3 characters regardless of text length?

A =MID(E5, LEN(E5)-2, 3)

B =MID(E5, LEN(E5)-3, 3)

C =RIGHT(E5, 3)

D =LEFT(E5, 3)

💡 Hint

Remember that MID's start position is 1-based and you must adjust for length.

To get the last 3 characters, start at position LEN(E5)-2 (because LEN(E5)-2, LEN(E5)-1, LEN(E5) are the last 3 characters). RIGHT(E5, 3) also works but the question asks for a formula that uses MID dynamically.

Practice

1. Which function would you use in Google Sheets to get the first 3 letters from the word "Banana"?

easy

A. RIGHT("Banana", 3)

B. LEFT("Banana", 3)

C. MID("Banana", 2, 3)

D. MID("Banana", 3, 3)

Solution

1. Step 1: Understand the function purpose

   The LEFT function extracts characters from the start of the text.

2. Step 2: Apply the function to the word

   LEFT("Banana", 3) returns the first 3 letters: "Ban".

3. Final Answer:

   LEFT("Banana", 3) -> Option B

4. Quick Check:

   LEFT = first letters [OK]

Hint: LEFT gets letters from start, RIGHT from end [OK]

Common Mistakes:

• Confusing LEFT with RIGHT function
• Using MID without correct start position
• Assuming MID always starts at 1

2. Which of these formulas correctly extracts the last 4 characters from the text "Spreadsheet"?

easy

A. RIGHT("Spreadsheet", 4)

B. LEFT("Spreadsheet", 4)

C. MID("Spreadsheet", 4, 4)

D. RIGHT("Spreadsheet", 5)

Solution

1. Step 1: Identify the function for last characters

   The RIGHT function extracts characters from the end of the text.

2. Step 2: Apply the function with correct number

   RIGHT("Spreadsheet", 4) returns the last 4 letters: "eet".

3. Final Answer:

   RIGHT("Spreadsheet", 4) -> Option A

4. Quick Check:

   RIGHT = last letters [OK]

Hint: RIGHT extracts from end; count how many letters you want [OK]

Common Mistakes:

• Using LEFT instead of RIGHT for last letters
• Choosing wrong number of characters
• Confusing MID start position with length

3. What is the result of the formula =MID("Adventure", 3, 4) in Google Sheets?

medium

A. "Adve"

B. "entu"

C. "dven"

D. "vent"

Solution

1. Step 1: Understand MID parameters

   MID(text, start, length) extracts length characters starting at position start.

2. Step 2: Apply to "Adventure"

   Starting at position 3: A(1), d(2), v(3), e(4), n(5), t(6), u(7), r(8), e(9). Extract 4 letters: v, e, n, t.

3. Final Answer:

   "vent" -> Option D

4. Quick Check:

   MID("Adventure",3,4) = "vent" [OK]

Hint: Count start position carefully, then take length letters [OK]

Common Mistakes:

• Starting count at 0 instead of 1
• Mixing up start position and length
• Assuming MID extracts from start or end

4. The formula =MID("Calculator", 0, 3) is used in a cell. What is the correct fix to get the first 3 letters?

medium

A. =MID("Calculator", 1, 3)

B. =LEFT("Calculator", 4)

C. =RIGHT("Calculator", 3)

D. =MID("Calculator", 3, 3)

Solution

1. Step 1: Identify the error in MID start position

   MID start position must be 1 or more; 0 is invalid and causes error.

2. Step 2: Correct the start position to 1

   Changing to MID("Calculator", 1, 3) extracts first 3 letters: "Cal".

3. Final Answer:

   =MID("Calculator", 1, 3) -> Option A

4. Quick Check:

   MID start must be ≥1 [OK]

Hint: MID start position must be 1 or more, never zero [OK]

Common Mistakes:

• Using 0 as start position in MID
• Using RIGHT or LEFT when MID is intended
• Changing length instead of start position

5. You have a list of product codes like "AB12345XYZ". You want to extract the middle 5 digits (positions 3 to 7). Which formula correctly extracts these digits?

hard

A. =RIGHT(A1, 5)

B. =LEFT(A1, 5)

C. =MID(A1, 3, 5)

D. =MID(A1, 5, 3)

Solution

1. Step 1: Identify the start position and length

   The digits start at position 3 and span 5 characters (positions 3,4,5,6,7).

2. Step 2: Use MID with correct parameters

   =MID(A1, 3, 5) extracts 5 characters starting at position 3, which are the digits.

3. Final Answer:

   =MID(A1, 3, 5) -> Option C

4. Quick Check:

   MID extracts middle characters by start and length [OK]

Hint: MID(text, start, length) extracts middle part exactly [OK]

Common Mistakes:

• Using LEFT or RIGHT instead of MID for middle extraction
• Wrong start position or length values
• Assuming digits are always at start or end