The Trim() function will clean up both leading and trailing spaces:
Example
Trim(A1) will turn "cat " (without quotes) into "cat".
To use it to clean up your data:
1) Insert your formula in the first vacant column.
2) Copy down.
3) Copy the result
4) Over the original bad values, Edit/ Paste Special/ Values
The following custom function will trim trailing blanks only:
Function RightTrim(c As Range)
RightTrim = RTrim(c)
End Function
To use the above you would need to create a macro:
a) Follow the steps under "Create a macro using Microsoft Visual Basic" on
the following page
http://office.microsoft.com/en-us/excel/HP052047111033.aspx
Copy and paste the above three lines of codes at step (3)
b) After doing this, enter it the same way as any inbuilt function. Example
=RightTrim(A1)