LEFT, RIGHT, MID extraction in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to extract the first 3 characters from cell A1 using the LEFT function.

Excel

=LEFT(A1, [1])

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

A1

B5

C3

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 returns an empty string.

Using 1 extracts only the first character.

The LEFT function extracts characters from the start of the text. Using 3 extracts the first three characters.

2fill in blank

medium

Complete the formula to extract the last 4 characters from cell B2 using the RIGHT function.

Excel

=RIGHT(B2, [1])

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

A4

B1

C6

D2

Attempts:

3 left

💡 Hint

Common Mistakes

Using 2 extracts only two characters.

Using 6 may extract more characters than exist.

The RIGHT function extracts characters from the end of the text. Using 4 extracts the last four characters.

3fill in blank

hard

Fix the error in the MID formula to extract 5 characters starting from the 3rd character in cell C3.

Excel

=MID(C3, [1], 5)

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

A5

B3

C1

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 causes an error because positions start at 1.

Using 1 extracts from the first character, not the third.

The MID function needs the starting position as the second argument. To start at the 3rd character, use 3.

4fill in blank

hard

Fill both blanks to extract 4 characters starting from the 2nd character in cell D4 using MID.

Excel

=MID(D4, [1], [2])

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

A2

B3

C4

D5

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the start position and length.

Using 5 extracts too many characters.

The MID function's second argument is the start position (2), and the third argument is the number of characters to extract (4).

5fill in blank

hard

Fill all three blanks to extract 3 characters starting from position 4 using MID and the first character using LEFT from cell E5.

Excel

=MID(E5, [1], [2]) & LEFT(E5, [3])

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

A4

B3

C2

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up the start position and length.

Using 2 for LEFT extracts too many characters.

The MID function starts at position 4 and extracts 3 characters. The LEFT function extracts 1 character from the 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