Remove Leading and Trailing Whitespace from Excel Cells

1
votes

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.

3
.netexcelvbatrim
Cells(1, 1) = Trim(" test test test ") works... but ah-ha you also want carriage returns and newlines... I don't think trim does that in VBA. So you'll need to simply use the replace command for chr(10) and chr(13) Something like Cells(1, 1) = Replace(Replace(Trim(" test " + Chr(10) + Chr(13) + " test test "), Chr(10), ""), Chr(13), "") – Cody Ｇ
Also, see stackoverflow.com/questions/2964769/… for how to remove all non printing characters, or support.office.com/en-us/article/… for microsoft's take on it, (see the CLEAN worksheet function) – Cody Ｇ
Try adding the "Microsoft VBScript Regular Expressions 5.5" reference and using RegEx to clean up your text. ? – Cody Ｇ

3 Answers

1
votes

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.

1
votes

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

1
votes

I hope your issue is resolved by now. I'm still going to put my answer out there for those who might still need it. I did too, and only came across this after extensive research, since most of the answers used loop which I generally want to avoid at all cost; at least the the explicit looping!

So, for me, these simple lines of code worked like a charm:

Dim Rng As Range
Set Rng = Range("A1").CurrentRegion 'Select the Range based on your criteria, preferably smaller
Rng.Value = Application.Trim(Rng)

Hope it helps :)