What is a formula to remove the whitespace from an Excel cell when trim doesn't work?

I have a table of Excel data that I copied from an html table in Internet Explorer. A column of table values all seem to have a space at the end of them, but Trim won't remove it. When I paste-special the value of the result of the Trim function, it still has the whitespace on the end.

Could it be some other sort of whitespace character that Trim doesn't remove?

Solution 1:

Character 160 is a non-breaking space, a character designed to look like a space but still be part of the word, forcing the adjacent words to stay together. You can convert them to regular spaces and then use trim.

Assuming the text is in A1, use:

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

Solution 2:

If it just has one space and let's assume that the column is A.

A1= "CELL A1 "
A2= "CELL A2 "

Then B1 would be =LEFT(A1, LEN(A1)-1)
Then B2 would be =LEFT(A2, LEN(A2)-1)