1

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.

Zoe
  • 27,060
  • 21
  • 118
  • 148
Brian
  • 1,201
  • 2
  • 14
  • 26
  • 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 Ｇ Jun 13 '16 at 15:48
  • Also, see https://stackoverflow.com/questions/2964769/trim-cells-using-vba-in-excel for how to remove all non printing characters, or https://support.office.com/en-us/article/Remove-spaces-and-nonprinting-characters-from-text-023f3a08-3d56-49e4-bf0c-fe5303222c9d for microsoft's take on it, (see the CLEAN worksheet function) – Cody Ｇ Jun 13 '16 at 16:00
  • 1
    Try adding the "Microsoft VBScript Regular Expressions 5.5" reference and using RegEx to clean up your text. ? – Cody Ｇ Jun 13 '16 at 18:04

3 Answers3

1

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.

xarxziux
  • 371
  • 4
  • 13
  • Thanks for the input. However, I do not want to remove *all* carriage returns and line feeds. I only want to remove carriage returns and line feeds from the beginning and end of the cell. – Brian Jun 13 '16 at 16:55
  • If the raw data is consistent then you could build a series of nested if statements, or clean it in stages. For a more complex situation you may be better off cleaning the data before bringing it into Excel. Are you able to post some samples? – xarxziux Jun 13 '16 at 19:22
  • The data was originally entered in Excel. Most cells contain a couple paragraphs separated by carriage returns and/or line feeds. My problem is that the person who typed the information in Excel frequently put blank spaces before the paragraphs of text or after the paragraphs of text. The functionality I want is exactly what is done by .NET's String.Trim method. – Brian Jun 14 '16 at 12:18
1

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 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 :)

V J
  • 11
  • 4
0

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

Naveed Khan
  • 338
  • 4
  • 16