Trimming VBA Columns

Ananthak275

Board Regular
Joined
Aug 22, 2020
Messages
56
Office Version
  1. 2013
Platform
  1. Windows
  2. MacOS
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 ", ", ","
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,283
Office Version
  1. 365
Platform
  1. Windows
What column do you want this to work on?
 

Ananthak275

Board Regular
Joined
Aug 22, 2020
Messages
56
Office Version
  1. 2013
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
Using values of " a b c d " and " e F g H ", this works for me:
Code:
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
AB
1ValueValue
2a b c dabcd
3e F g HeFgH
4a b c dabcd
5e F g HeFgH
6a b c dabcd
7a b c dabcd
8a b c dabcd
9a b c dabcd
10a b c dabcd
11a b c dabcd
12a b c dabcd
13a b c dabcd
14a b c dabcd
15a b c dabcd
16a b c dabcd
Sheet2
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,283
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok, how about
VBA Code:
   With Range("E2", Range("E" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",substitute(@,"" "",""""))", "@", .Address))
   End With
 

Ananthak275

Board Regular
Joined
Aug 22, 2020
Messages
56
Office Version
  1. 2013
Platform
  1. Windows
  2. MacOS
Ok, how about
VBA Code:
   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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,283
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Aug 22, 2020
Messages
56
Office Version
  1. 2013
Platform
  1. Windows
  2. MacOS
- 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
 

Attachments

  • 1622650779041.png
    1622650779041.png
    14.5 KB · Views: 4

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,658
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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...
VBA Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,486
Messages
5,770,373
Members
425,612
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top