Excel
All Top 50 Excel & Business Modeling Questions#22
Splitting Full Names into First and Last with LEFT, RIGHT, and FIND
MediumDeloitteInterview Question #22
Asked at DeloitteIn Column A, you have Full Names formatted as 'First Last' (e.g. 'Jane Doe', 'Alexander Hamilton'). Write formulas in Column B to extract the First Name and in Column C to extract the Last Name, adapting dynamically to varying character lengths.
Input Table: NameList (A1:A3)
2 rows preview| Full Name (Col A) |
|---|
| Jane Doe |
| Alexander Hamilton |
Expected Output Structure2 rows
| First Name (Col B) | Last Name (Col C) |
|---|---|
| Jane | Doe |
| Alexander | Hamilton |
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 | Full Name (Col A) | |||||||||
| 2 | Jane Doe | Target [Enter Formula] | ||||||||
| 3 | Alexander Hamilton | |||||||||
| 4 | ||||||||||
| 5 | ||||||||||
| 6 | ||||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
This question demonstrates a technique that can't be graded as a single cell formula (it needs another sheet, a real PivotTable, or compares two methods side-by-side). Read the explanation below instead.