LEFT, RIGHT, MID in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

This data shows dessert names in column A.

Cell	Value
A1	ApplePie
A2	BananaSplit
A3	CherryTart

Formula Trace

=LEFT(A1,5) & "-" & RIGHT(A2,5) & "-" & MID(A3,7,4)

Step 1: LEFT(A1,5)

Step 2: RIGHT(A2,5)

Step 3: MID(A3,7,4)

Step 4: "Apple" & "-" & "Split" & "-" & "Tart"

Cell Reference Map

     A       
1 | ApplePie  
2 | BananaSplit
3 | CherryTart

Formula references A1, A2, and A3 cells.

Cells A1, A2, and A3 contain the dessert names used by LEFT, RIGHT, and MID functions.

Result

     A           B
1 | ApplePie  | Apple-Split-Tart
2 | BananaSplit|
3 | CherryTart|

The formula in B1 shows 'Apple-Split-Tart' combining parts from A1, A2, and A3.

Sheet Trace Quiz - 3 Questions

What does LEFT(A1,5) return if A1 contains 'ApplePie'?

A Pie

B Apple

C ApplePie

D P

Key Result

LEFT(text, n) extracts first n characters; RIGHT(text, n) extracts last n characters; MID(text, start, length) extracts substring from start position with given length.

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