LEFT, RIGHT, MID extraction in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales assistant at a retail company.

📋 Request: Your manager wants you to extract specific parts of product codes to analyze product categories and batch numbers.

📊 Data: You have a list of product codes in one column. Each code contains a category prefix, a batch number in the middle, and a suffix code at the end.

🎯 Deliverable: Create formulas to extract the category prefix (first 3 letters), the batch number (middle 4 digits), and the suffix code (last 2 letters) into separate columns.

Sample Data

Product Code
ABC1234XY
DEF5678YZ
GHI9012WX
JKL3456UV
MNO7890ST
PQR2345QR
STU6789OP
VWX0123LM

1

Step 1: Extract the category prefix (first 3 letters) from the product code in cell A2.

=LEFT(A2, 3)

Expected Result

ABC

2

Step 2: Extract the batch number (4 digits in the middle) from the product code in cell A2. The batch number starts at the 4th character.

=MID(A2, 4, 4)

Expected Result

1234

3

Step 3: Extract the suffix code (last 2 letters) from the product code in cell A2.

=RIGHT(A2, 2)

Expected Result

XY

4

Step 4: Copy the formulas down the columns to extract parts for all product codes in the list.

Drag the formulas from cells B2, C2, and D2 down to row 9.

Expected Result

All product codes are split into prefix, batch number, and suffix in columns B, C, and D.

Final Result

Product Code | Prefix | Batch | Suffix
-------------|--------|-------|--------
ABC1234XY    | ABC    | 1234  | XY
DEF5678YZ    | DEF    | 5678  | YZ
GHI9012WX    | GHI    | 9012  | WX
JKL3456UV    | JKL    | 3456  | UV
MNO7890ST    | MNO    | 7890  | ST
PQR2345QR    | PQR    | 2345  | QR
STU6789OP    | STU    | 6789  | OP
VWX0123LM    | VWX    | 0123  | LM

✓The category prefix helps identify the product type quickly.

✓Batch numbers can be used to track production runs.

✓Suffix codes may indicate special product features or packaging.

Bonus Challenge

Create a formula to combine the extracted parts back into the original product code.

Show Hint

Use the CONCATENATE function or the & operator to join prefix, batch, and suffix.