Excel data cleaning interview question

Clean with formulas in a new column, one problem at a time: TRIM for stray spaces, TEXTBEFORE and TEXTAFTER to split and reorder text, VALUE for numbers stored as text, and DATE with LEFT, MID and RIGHT for dates written as plain digits. For a name written “Hart, Lena”, =TRIM(TEXTAFTER(A2,",")&" "&TEXTBEFORE(A2,",")) gives “Lena Hart”; in Excel 2021 or older, FIND with LEFT and MID does the same split. The formulas repeat on next month’s export, which retyping never will.

The question

“We get this customer export every month from an old system. Names come as ‘Surname, First name’ with random spaces, the amounts won’t add up, and the dates aren’t real dates. Clean it so we can use it, in a way that works next month too.”

The interviewer is less interested in any one function than in whether you can see what’s wrong with each column.

What they’re really testing

• That you diagnose before you fix. Is it spaces? Text that looks like a number? A date that’s really a number?
• That you keep the raw data. Clean in a new column, so the original is there to check against.
• That your fix repeats. Formulas, not hand edits, because the export comes back every month.
• That you know the traps. Spaces you can’t see, a number that’s secretly text, and a lookup that fails because of either.

Extra spaces

TRIM removes spaces at the start and end and squeezes every run of spaces between words into one:

B2fx=TRIM(A2)

Row	A	B
1	Raw name	Clean name
2	Lena Hart	Lena Hart

Spaces matter more than they look: “Lena Hart “ with a trailing space doesn’t match “Lena Hart” in a lookup, so the lookup question fails with #N/A for no visible reason. =LEN(A2) exposes them: compare it with the number of letters you can see.

One trap to mention: text copied from a web page often has non-breaking spaces, which TRIM doesn’t remove. Replace them with ordinary spaces first, using SUBSTITUTE with CHAR(160).

Names in the wrong order

“Shah, Priya” needs to become “Priya Shah”. TEXTAFTER takes what follows the comma, TEXTBEFORE what precedes it. Join them with a space between and wrap the whole thing in TRIM, which removes the spaces that came along with the comma:

Without the outer TRIM, the result starts with the two spaces that followed the comma, and the cleaned name still won’t match anything:

The lengths tell the story: the untrimmed name is two characters longer than “Priya Shah”. In Excel 2021 or older, which have neither function, find the comma with FIND and cut with LEFT and MID instead (see the FAQ).

Numbers stored as text

Exports often bring numbers in as text: left-aligned, sometimes with a space, and ignored by SUM. Column B here is text, so its total is 0:

=VALUE(TRIM(B2)) in a new column turns each one back into a number, and the new column adds up as it should:

C5fx=SUM(C2:C4)

Row	A	B	C
1	Invoice	Amount (text)	Amount
2	INV-301	1250	1250
3	INV-302	980	980
4	INV-303	2400	2400
5	Total		4630

A double minus in front, =--TRIM(B2), does the same. Data › Text to Columns › Finish fixes a whole column in place, if the interviewer asks for a non-formula way.

Dates that aren’t dates

A date written as 20260315 looks like a date to you and like a number (or text) to Excel. Formatting it as a date doesn’t help: read as a date, 20260315 is far past the last day Excel can show, so the cell just fills with # signs. Take it apart and build a real date:

B2fx=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

Row	A	B
1	Raw date	Date
2	20260315	15 Mar 2026

LEFT takes the year, MID the month, RIGHT the day, and DATE builds a date you can sort, filter and subtract. Once it’s a real date, TEXT can show it any way the report needs.

Common wrong answers, and why they fail

1. Cleaning by hand. Fixed today, broken next month. Interviewers ask “and next month?” on purpose.
2. TRIM on part of the result only. The spaces come back from whichever part you didn’t trim.
3. Splitting at “, “ (comma and space). Works until a row has extra spaces after the comma, or none. Split at the comma and TRIM.
4. Formatting text as a number. Changing a cell’s format doesn’t change what’s in it. The text stays text until VALUE (or Text to Columns) converts it.
5. Overwriting the raw column. Keep it until the cleaned column is checked.

Practise it: a name for a mail merge

The other direction this time: the export has “First name Surname” with stray spaces, and the mail merge wants “Surname, First name”. Each name here is one first name and one surname. We check the result, then try a name that’s already clean and one with spaces at both ends.

Your task: In B2, turn the name in A2 into "Surname, First name" for a mail merge: no extra spaces, the surname first, then a comma and a space, then the first name.

Graded live

B2fx

Row	A	B
1	Raw name	Mail merge name
2	Lena Hart

Write the formula in the bar above, then press Check or Enter.

Hint

TRIM(A2) cleans the name. In it, TEXTAFTER(…," ") is the surname and TEXTBEFORE(…," ") the first name: join them with ", ".

Solution

=TEXTAFTER(TRIM(A2)," ")&", "&TEXTBEFORE(TRIM(A2)," ") Any formula that returns the same result passes.

Check

How to answer it in the interview

Walk the columns in order and name the problem before the fix: “Names have extra spaces and are surname-first, so TRIM and a split at the comma. Amounts are text, so VALUE. Dates are eight digits, so DATE with LEFT, MID and RIGHT. All in new columns, so it repeats next month.” That answer shows the habit they’re hiring for. More questions like this one: Excel interview questions.