LEFT, RIGHT, MID extraction in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Extracting first 4 characters with LEFT

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

A "Sheet"

B "Spre"

C "2024"

D "read"

💡 Hint

LEFT extracts characters starting from the beginning of the text.

The LEFT function takes the first 4 characters from the left side of the text in A1, which are "S", "p", "r", "e" forming "Spre".

📊 Formula Result

intermediate

Extract last 3 characters with RIGHT

If cell B2 contains "Invoice12345", what does the formula =RIGHT(B2,3) return?

A "345"

B "123"

C "Inv"

D "ice"

💡 Hint

RIGHT extracts characters from the end of the text.

RIGHT(B2,3) takes the last 3 characters from the text "Invoice12345", which are "3", "4", "5" making "345".

📊 Formula Result

advanced

Extract middle characters with MID

Cell C3 contains "CustomerID2024". What is the result of =MID(C3,9,2)?

A "st"

B "er"

C "20"

D "ID"

💡 Hint

MID extracts characters starting at a position you choose.

MID(C3,9,2) starts at the 9th character of "CustomerID2024" which is "I", and extracts 2 characters: "I" and "D".

🎯 Scenario

advanced

Extracting area code from phone number

You have phone numbers in column D in the format [phone]. Which formula extracts the area code (the 3 digits inside the parentheses)?

A =RIGHT(D1,3)

B =LEFT(D1,3)

C =MID(D1,2,3)

D =MID(D1,1,3)

💡 Hint

Area code starts after the first character which is an opening parenthesis.

The area code is inside parentheses, so starting at character 2 and taking 3 characters extracts the digits inside the parentheses.

❓ Function Choice

expert

Choosing the correct formula for variable length extraction

You want to extract the first word from a cell E1 that contains a full name like "John Smith". The first word length varies. Which formula correctly extracts the first word?

A =LEFT(E1,FIND(" ",E1)-1)

B =MID(E1,1,FIND(" ",E1))

C =RIGHT(E1,FIND(" ",E1)-1)

D =MID(E1,FIND(" ",E1)+1,LEN(E1))

💡 Hint

Use FIND to locate the space and LEFT to get characters before it.

FIND(" ",E1) finds the position of the space. LEFT extracts characters from the start up to one less than that position, giving the first word.

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