Excel
All Top 50 Excel & Business Modeling Questions#25
Standardizing Dirty Phone Numbers and Strings with Nested SUBSTITUTE
MediumFlipkartInterview Question #25
Asked at FlipkartCustomer phone numbers in Column A arrive in varied formats: `(555) 123-4567`, `555.123.4567`, and `+1-555-123-4567`. Write a formula in Column B to strip all parentheses, spaces, dots, dashes, and the `+1` prefix to leave exactly 10 pure digits.
Input Table: RawContacts (A1:A4)
3 rows preview| Raw Contact (Col A) |
|---|
| (555) 123-4567 |
| 555.123.4567 |
| +1-555-123-4567 |
Expected Output Structure3 rows
| Normalized 10-Digit String (Col B) |
|---|
| 5551234567 |
| 5551234567 |
| 5551234567 |
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 Contact (Col A) | |||||
| 2 | (555) 123-4567 | Target [Enter Formula] | ||||
| 3 | 555.123.4567 | |||||
| 4 | +1-555-123-4567 | |||||
| 5 | ||||||
| 6 | ||||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect