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