Trimming VBA Columns

[Ananthak275]

Hi, this is a build on the code from this page (Trim Column in VBA). The second line removes the leading, trailing and collapses multiple adjacent internal spaces down to single spaces. But i need to collapse all internal spaces which is why i added the 3rd line (using the replace function).

While this code works, I'm trying to see if there is a simpler way of being able to remove the leading, trailing and collapse all internal spaces down to zero?

Addr = ColumnLetter & "2" & ":" & ColumnLetter & Cells(Rows.Count, ColumnLetter).End(xlUp).Row
Range(Addr) = Evaluate("IF(" & Addr & "="""","""",TRIM(" & Addr & "))")
ActiveSheet.Range(Addr).Replace ", ", ","

 

[Fluff]

What column do you want this to work on?

 

[Ananthak275]

What column do you want this to work on?

My column is actually dynamic. Its converting a number to letter for other parts of my code. You can use any column. the code below is how its set up originally

Addr = "E1:E" & Cells(Rows.Count, "E").End(xlUp).Row
Range(Addr) = Evaluate("IF(" & Addr & "="""","""",TRIM(" & Addr & "))")
ActiveSheet.Range(Addr).Replace ", ", ","

 

[JackDanIce]

Using values of " a b c d " and " e F g H ", this works for me:

Sub test()
Dim LR As Long: LR = Cells(Rows.Count).End(xlUp).row
[B1].Resize(LR).Value = Application.Substitute([A1].Resize(LR), " ", "")
End Sub

Other.xlsm
	A	B
1	Value	Value
2	a b c d	abcd
3	e F g H	eFgH
4	a b c d	abcd
5	e F g H	eFgH
6	a b c d	abcd
7	a b c d	abcd
8	a b c d	abcd
9	a b c d	abcd
10	a b c d	abcd
11	a b c d	abcd
12	a b c d	abcd
13	a b c d	abcd
14	a b c d	abcd
15	a b c d	abcd
16	a b c d	abcd
Sheet2

 

[Fluff]

Ok, how about

   With Range("E2", Range("E" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",substitute(@,"" "",""""))", "@", .Address))
   End With

 

[Ananthak275]

Ok, how about

   With Range("E2", Range("E" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",substitute(@,"" "",""""))", "@", .Address))
   End With

Ah i just realized one thing, sorry about this. can you make it that it removes leading, heading and spaces after and before commas? Because I've noticed that i have multiple words between commas that are now one

 

[Fluff]

Please post some sample data showing expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Ananthak275]

- Does this help?
Row1: Removes space after commas but keeps the space between "Tree House"
Row2: Removes leading space before soil/
Row3: Removes trailing spaces after the word "simplicity".
Row4: Collapses multiple adjacent internal spaces down to zero
Row5: Similarly as Row 1, keeps "Apple Farm" together and removes the space after the comma

 

[Rick Rothstein]

I think I am going crazy. I just answered this same question (by the same author I think) in another thread here in this forum and the OP replied my code worked perfectly. Now I cannot find that thread??? Here is what I posted there...

Sub NoSpacesColumnE()
  Dim Addr As String
  Addr = "E1:E" & Cells(Rows.Count, "E").End(xlUp).Row
  Range(Addr) = Evaluate("SUBSTITUTE(SUBSTITUTE(TRIM(" & Addr & "),"", "","",""),"" ,"","","")")
End Sub