Remove leading or trailing spaces in an entire column of data

Quite often the issue is a non-breaking space – CHAR(160) – especially from Web text sources -that CLEAN can’t remove, so I would go a step further than this and try a formula like this which replaces any non-breaking spaces with a standard one

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

Ron de Bruin has an excellent post on tips for cleaning data here

You can also remove the CHAR(160) directly without a workaround formula by

• Edit …. Replace your selected data,
• in Find What hold ALT and type 0160 using the numeric keypad
• Leave Replace With as blank and select Replace All