LEFT, RIGHT, MID extraction in Excel - Cell-by-Cell Formula Trace

Sample Data

Column A contains text strings. Column B contains numbers representing how many characters to extract.

Cell	Value
A1	HelloWorld
A2	ExcelFun
A3	Spreadsheet
B1	5
B2	3
B3	6

Formula Trace

=LEFT(A1, B1)

Step 1: LEFT("HelloWorld", 5)

Cell Reference Map

    A          B
1 | HelloWorld | 5 |
2 | ExcelFun  | 3 |
3 | Spreadsheet| 6 |

The formula uses cell A1 for the text and B1 for the number of characters to extract from the left.

Result

    A          B     C
1 | HelloWorld | 5 | Hello |
2 | ExcelFun  | 3 |       |
3 | Spreadsheet| 6 |       |

The formula in C1 shows "Hello", the first 5 characters extracted from A1.

Sheet Trace Quiz - 3 Questions

What does the formula =LEFT(A2, B2) return?

A "Fun"

B "Exc"

C "Excel"

D "FunExc"

Key Result

LEFT(text, n) extracts first n characters; RIGHT(text, n) extracts last n characters; MID(text, start, n) extracts n characters starting at position start.

Practice

1. Which Excel function extracts the first 3 characters from the text in cell A1?

easy

A. =MID(A1, 3, 3)

B. =LEFT(A1, 3)

C. =RIGHT(A1, 3)

D. =MID(A1, 2, 3)

Solution

1. Step 1: Understand LEFT function usage

   The LEFT function extracts characters starting from the beginning of the text. Using 3 as the second argument means it takes the first 3 characters.

2. Step 2: Compare options

   =LEFT(A1, 3) uses =LEFT(A1, 3), which correctly extracts the first 3 characters. =RIGHT(A1, 3) extracts from the end, =MID(A1, 3, 3) starts at position 3, and =MID(A1, 2, 3) starts at position 2.

3. Final Answer:

   =LEFT(A1, 3) -> Option B

4. Quick Check:

   LEFT gets first characters = LEFT(A1, 3) [OK]

Hint: Use LEFT for start, RIGHT for end, MID for middle [OK]

Common Mistakes:

• Using RIGHT instead of LEFT for start characters
• Confusing MID start position with length
• Using MID with wrong start position

2. Which formula correctly extracts the last 4 characters from cell B2?

easy

A. =RIGHT(B2, 4)

B. =LEFT(B2, 4)

C. =MID(B2, LEN(B2)-4, 4)

D. =MID(B2, 4, 4)

Solution

1. Step 1: Understand RIGHT function usage

   The RIGHT function extracts characters from the end of the text. Using 4 as the second argument means it takes the last 4 characters.

2. Step 2: Evaluate options

   =RIGHT(B2, 4) uses =RIGHT(B2, 4), which directly extracts the last 4 characters. =LEFT(B2, 4) extracts from the start, =MID(B2, LEN(B2)-4, 4) has an off-by-one error in start position, and =MID(B2, 4, 4) extracts from position 4 but not from the end.

3. Final Answer:

   =RIGHT(B2, 4) -> Option A

4. Quick Check:

   RIGHT gets last characters = RIGHT(B2, 4) [OK]

Hint: RIGHT gets last characters; use length number [OK]

Common Mistakes:

• Using LEFT instead of RIGHT for last characters
• Incorrect MID start position calculation
• Confusing length with start position

3. If cell C3 contains the text "Spreadsheet", what is the result of =MID(C3, 4, 5)?

medium

A. "pread"

B. "sheet"

C. "dshee"

D. "eadsh"

Solution

1. Step 1: Identify the MID function parameters

   MID(text, start_num, num_chars) extracts characters starting at position 4 for 5 characters from "Spreadsheet".

2. Step 2: Extract characters from "Spreadsheet"

   Characters: S(1) p(2) r(3) e(4) a(5) d(6) s(7) h(8) e(9) e(10) t(11). Starting at 4: e, a, d, s, h -> "eadsh".

3. Final Answer:

   "eadsh" -> Option D

4. Quick Check:

   MID extracts from middle = "eadsh" [OK]

Hint: Count start position carefully for MID [OK]

Common Mistakes:

• Starting count at 0 instead of 1
• Mixing up start position and length
• Miscounting characters in the text

4. The formula =MID(D4, 0, 3) is used to extract characters from cell D4. What is the issue with this formula?

medium

A. Start position cannot be zero; it must be 1 or more.

B. Length cannot be less than 3.

C. MID cannot extract from cell references.

D. Formula should use LEFT instead of MID.

Solution

1. Step 1: Check MID function start position rules

   The start position in MID must be 1 or greater. Zero is invalid and causes an error.

2. Step 2: Analyze the given formula

   The formula uses start position 0, which is not allowed. Length 3 is valid, and MID works with cell references.

3. Final Answer:

   Start position cannot be zero; it must be 1 or more. -> Option A

4. Quick Check:

   MID start position ≥ 1 [OK]

Hint: MID start position must be ≥ 1 [OK]

Common Mistakes:

• Using zero or negative start position
• Confusing length with start position
• Thinking MID can't use cell references

5. You have a list of product codes in column A like "AB12345XYZ". You want to extract the middle 5 digits (positions 3 to 7). Which formula in cell B1 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 required substring

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

2. Step 2: Use MID function with correct parameters

   MID(text, start_num, num_chars) = MID(A1, 3, 5) extracts characters from position 3 for 5 characters.

3. Final Answer:

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

4. Quick Check:

   MID extracts middle substring = MID(A1, 3, 5) [OK]

Hint: MID(start, length) extracts middle substring easily [OK]

Common Mistakes:

• Using LEFT or RIGHT instead of MID for middle parts
• Swapping start position and length
• Incorrect start position or length values