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
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 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 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect