TRIM in Excel: clean extra spaces without joining words

Keep a copy of the original labels

A workshop imports a customer list. Some labels have spaces at the beginning or end, while others have several spaces between words. Put the original values in column A and the cleaned results in column B so you can compare them before replacing anything.

For this example, A2 contains two spaces, Cedar, three spaces, Workshop, then two more spaces. A3 contains Oak followed by a nonbreaking space and Studio. A4 contains Maple Works with one ordinary space. A5 contains three ordinary spaces and nothing else.

Remove ordinary extra spaces

TRIM removes leading and trailing ordinary spaces and leaves one space between words. The result for A2 is Cedar Workshop. The existing single space in Maple Works is preserved. A cell containing only ordinary spaces becomes empty text.

=TRIM(A2)

Do not delete every space

Replacing every ordinary space with empty text would turn Cedar Workshop into CedarWorkshop. That is a different label, not simply a tidier version. It could prevent a later lookup against the original customer name.

Use a separate rule for identifiers whose spaces are forbidden. Names, addresses and product descriptions may legitimately contain spaces, so a universal remove-all-spaces rule is risky.

Handle a space copied from a web page

Excel TRIM does not remove the nonbreaking space with character value 160 by itself. Replace that character with an ordinary space, then trim. For A3 the intended result is Oak Studio. The space between the words remains.

In the practice input, the gap in A3 looks much like a normal space. It is intentionally a different character. This is why visual inspection alone can miss the cause of a failed match.

=TRIM(SUBSTITUTE(A3,CHAR(160)," "))

Check what the cleanup did

Compare the cleaned value with the original using EXACT. For A4, =EXACT(A4,TRIM(A4)) returns TRUE because Maple Works was already clean. For A2 it returns FALSE because the spaces changed.

An empty result is not proof that the original cell was empty. A5 contains spaces; TRIM returns empty text. Decide whether such a row should be removed, repaired or kept as a missing label.

Cleanup also does not merge duplicate customer records. If two different source rows both become Cedar Workshop, inspect their customer IDs before deciding that they describe the same customer.

Try a different input

Change A2 to Pine followed by three ordinary spaces and Repairs. The result becomes Pine Repairs. Reset the exercise and try the nonbreaking-space formula on A3. Keep the raw values until you have reviewed both the changed rows and any newly matching labels.

Further reading

• Microsoft: TRIM function and its nonbreaking-space limitation