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
B2fx
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Raw Contact (Col A) | |||||||||
| 2 | (555) 123-4567 | Target [Enter Formula] | ||||||||
| 3 | 555.123.4567 | |||||||||
| 4 | +1-555-123-4567 | |||||||||
| 5 | ||||||||||
| 6 | ||||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
Click on target cell B2 and enter your formula above.Shortcut: Click "Load Solution" to inspect