LEFT, RIGHT, MID in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a customer service analyst at an online retail company.

📋 Request: Your manager wants you to extract specific parts of customer order codes to analyze product categories and order batches.

📊 Data: You have a list of order codes in one column. Each code is a mix of letters and numbers. The first 3 letters represent the product category, the next 4 digits represent the batch number, and the last 2 letters represent the region code.

🎯 Deliverable: Create a sheet that shows the original order code and separate columns extracting the product category, batch number, and region code using LEFT, MID, and RIGHT formulas.

Sample Data

Order Code
ABC1234XY
DEF5678YZ
GHI9012WX
JKL3456YZ
MNO7890XY
PQR2345WX
STU6789YZ
VWX0123XY

1

Step 1: In cell B2, extract the product category (first 3 letters) from the order code in A2 using the LEFT function.

=LEFT(A2, 3)

Expected Result

ABC

2

Step 2: In cell C2, extract the batch number (4 digits starting from the 4th character) from the order code in A2 using the MID function.

=MID(A2, 4, 4)

Expected Result

1234

3

Step 3: In cell D2, extract the region code (last 2 letters) from the order code in A2 using the RIGHT function.

=RIGHT(A2, 2)

Expected Result

XY

4

Step 4: Copy the formulas in B2, C2, and D2 down to rows 3 through 9 to extract parts for all order codes.

Drag down or copy-paste formulas from B2:D2 to B3:D9

Expected Result

Columns B, C, D filled with extracted parts for all order codes

Final Result

Order Code | Product Category | Batch Number | Region Code
---------------------------------------------------------
ABC1234XY  | ABC              | 1234         | XY
DEF5678YZ  | DEF              | 5678         | YZ
GHI9012WX  | GHI              | 9012         | WX
JKL3456YZ  | JKL              | 3456         | YZ
MNO7890XY  | MNO              | 7890         | XY
PQR2345WX  | PQR              | 2345         | WX
STU6789YZ  | STU              | 6789         | YZ
VWX0123XY  | VWX              | 0123         | XY

✓The LEFT function extracts the product category from the start of the order code.

✓The MID function extracts the batch number from the middle of the order code.

✓The RIGHT function extracts the region code from the end of the order code.

✓This separation helps analyze orders by category, batch, and region easily.

Bonus Challenge

Create a new column that combines the product category and region code with a dash in between (e.g., ABC-XY).

Show Hint

Use the CONCATENATE function or the & operator: =B2 & "-" & D2

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