Excel
All Top 50 Excel & Business Modeling Questions#23
Sanitizing Unseen Whitespace and Non-Breaking Spaces in Raw Exports
EasyAccentureInterview Question #23
Asked at AccentureA web ERP export of Account Codes in `A2:A100` produces `#N/A` in VLOOKUP despite matching visually. The export contains leading spaces, trailing spaces, and web non-breaking spaces (`CHAR(160)`). Write a formula in `B2` to completely sanitize the text.
Input Table: RawExport (A1:A3)
2 rows preview| Raw Account String (Col A) |
|---|
| ACC-901 |
| ACC-902 |
Expected Output Structure2 rows
| Cleaned Account Code (Col B) |
|---|
| ACC-901 |
| ACC-902 |
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 Account String (Col A) | |||||||||
| 2 | ACC-901 | Target [Enter Formula] | ||||||||
| 3 | ACC-902 | |||||||||
| 4 | ||||||||||
| 5 | ||||||||||
| 6 | ||||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
Click on target cell B2 and enter your formula above.Shortcut: Click "Load Solution" to inspect