Excel
All Top 50 Excel & Business Modeling Questions#26
Parsing Multi-Delimiter Strings Dynamically with TEXTSPLIT
MediumGoogleInterview Question #26
Asked at GoogleIn cell `A2`, an e-commerce tag string reads: `SKU101:45;SKU102:12;SKU103:89`. Write a single formula in `B2` using TEXTSPLIT to parse this into a 2-column table where Column 1 has the SKUs and Column 2 has the quantities.
Input Table: TagString (A2)
1 rows preview| Raw Tag String |
|---|
| SKU101:45;SKU102:12;SKU103:89 |
Expected Output Structure3 rows
| Parsed SKU (Col B) | Parsed Qty (Col C) |
|---|---|
| SKU101 | 45 |
| SKU102 | 12 |
| SKU103 | 89 |
Interview Context
Asked frequently in data analyst and business analyst technical rounds. Focus on clean filtering, optimal indexing usage, and unambiguous column selection.
Microsoft Excel 365
E2fx
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Raw Tag String | |||||
| 2 | SKU101:45;SKU102:12;SKU103:89 | Target [Enter Formula] | ||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect