Remove Leading and Trailing Whitespace from Excel

Question

I have an Excel spreadsheet with several cells that have leading or trailing whitespace (including spaces, non-breaking spaces, carriage returns, and new lines). I want to remove these white spaces. My Google searching suggests this can only be done with VBA. The VBA Trim function doesn't remove all types of whitespaces and may remove from the middle of the cell as well. The MSDN page for VBA's Trim function references .NET's String.Trim method which does exactly what I want. However, I'm not sure how to call String.Trim from VBA as suggested by the first link. How do I call the String.Trim method from Excel VBA?

EDIT: To clarify, I want to keep the whitespace (including carriage returns and new lines) in the middle of the cell. I only want to remove the characters from the beginning and the end of the cell.

Answer 1

I was having the same issue but i found this code and its working perfect. It removes leading and trailing space from column name .

foreach (DataColumn c in d.Columns)
                c.ColumnName = c.ColumnName.Trim();

Cheers

Answer 2

If you want to do this in Excel without having to go through VBA use:

=TRIM(CLEAN(A1))

Assuming you want to clean cell A1. From my tests this removes all tabs, carriage return and line feeds, but also converts multiple spaces to single ones so it may not be exactly what you want.