Excel
All Top 50 Excel & Business Modeling Questions#21
Combining Multi-Cell Strings with Delimiters Using TEXTJOIN
EasyAmazonInterview Question #21
Asked at AmazonYou have customer address components across Columns A through D (Street, Apt/Suite, City, State). In some rows, Apt/Suite is blank. Write a single formula in Column E using TEXTJOIN to combine the non-empty address parts separated by comma and space (', ').
Input Table: AddressRecords (A1:D3)
2 rows preview| Street (Col A) | Suite (Col B) | City (Col C) | State (Col D) |
|---|---|---|---|
| 742 Evergreen Terr | Suite 4B | Springfield | OR |
| 100 Main St | Seattle | WA |
Expected Output Structure2 rows
| Combined Address (Col E) |
|---|
| 742 Evergreen Terr, Suite 4B, Springfield, OR |
| 100 Main St, Seattle, WA |
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 | Street (Col A) | Suite (Col B) | City (Col C) | State (Col D) | ||
| 2 | 742 Evergreen Terr | Suite 4B | Springfield | OR | Target [Enter Formula] | |
| 3 | 100 Main St | Seattle | WA | |||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect