LEFT, RIGHT, MID extraction in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the LEFT function do in Excel?

The LEFT function extracts a specified number of characters from the start (left side) of a text string.

beginner

How do you extract the last 3 characters from a text string in Excel?

Use the RIGHT function with 3 as the number of characters, like this: RIGHT(text, 3).

beginner

Explain the MID function in Excel.

MID extracts characters from the middle of a text string. You specify the start position and how many characters to take, like MID(text, start_num, num_chars).

beginner

What will the formula =LEFT("Hello World", 5) return?

It returns "Hello" because it takes the first 5 characters from the left of the text "Hello World".

beginner

If cell A1 contains "Spreadsheet", what does =MID(A1, 6, 5) return?

It returns "sheet" because it starts at the 6th character and takes 5 characters from "Spreadsheet".

Which function extracts characters from the end of a text string?

A LEFT

B LEN

C RIGHT

D MID

RIGHT extracts characters from the end (right side) of a text string.

What does =LEFT("Excel", 2) return?

A "Ex"

B "el"

C "ce"

D "elc"

LEFT("Excel", 2) returns the first two characters: "Ex".

How do you extract 4 characters starting from the 3rd character in a text string?

A LEN(text, 4)

B LEFT(text, 4)

C RIGHT(text, 4)

D MID(text, 3, 4)

MID(text, 3, 4) extracts 4 characters starting at position 3.

If A1 contains "Data", what does =RIGHT(A1, 2) return?

A "ta"

B "at"

C "Da"

D at"

RIGHT(A1, 2) returns the last two characters: "ta".

Which formula extracts the first character of a text string?

A RIGHT(text, 1)

B LEFT(text, 1)

C MID(text, 2, 1)

D LEN(text)

LEFT(text, 1) extracts the first character from the left.

Describe how to use LEFT, RIGHT, and MID functions to extract parts of a text string in Excel.

Think about where you want to start extracting characters: beginning, end, or middle.

Give an example of a situation where you would use the MID function instead of LEFT or RIGHT.

Consider extracting a part that is not at the start or end.

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