Excel track · Exercise 59 of 86 Paid

Clean up an imported list

Tidy spacing and case with TRIM and PROPER, then build a lower-case key from the same text.

Advanced

Imported names with padding, doubled spaces and mixed case

Read-only data · screen 1 of 2 · answer in B2

In B2, tidy the imported name in A2 so it has no stray spaces and each word is capitalised.

	A	B
1	Imported	Answer
2	ANA LOPEZ
3	bo CHEN
4	CY patel
5	di OKORO

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

In B2, build a lower-case key from the imported name in A2 with a dot where the space was.

	A	B
1	Imported	Answer
2	ANA LOPEZ
3	bo CHEN
4	CY patel
5	di OKORO

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Reference answer

What it does

TRIM removes padding at both ends of the text and collapses runs of spaces inside it to one. PROPER then capitalises the first letter of each word. Nested as PROPER(TRIM(A2)), they turn the padded, shouted name in A2 into Ana Lopez.

The order matters more than the functions do. PROPER applied to doubled spaces tidies the case and leaves the spaces in place, so the cleaned name still fails a lookup. MATCH does not ignore a trailing space, and it returns #N/A for a name that looks identical on screen.

The second screen builds a match key from the same text. TRIM goes first, SUBSTITUTE swaps the one remaining space for a dot, and LOWER flattens the case to ana.lopez. A key column is what lets two lists that spell a name differently join on one cell.

Syntax

=PROPER(TRIM(text)) tidies spacing and case; nest SUBSTITUTE and LOWER for a key

Worked example

On Imported names with padding, doubled spaces and mixed case, =PROPER(TRIM(A2)) returns Ana Lopez.

=PROPER(TRIM(A2))

Common errors

Error codes for TRIM before PROPER, then TRIM, SUBSTITUTE and LOWER nested into a match key
Error	What it means and how to fix it
`#VALUE!`	A function is handed a whole range instead of one cell, which is not a single string to clean.
`#NAME?`	The function name is misspelled, and Excel returns #NAME?.
`Doubled dot`	SUBSTITUTE runs before TRIM and every run of spaces becomes a run of dots in the key.

At work

An imported name column that has to match another list before anything joins to it.