trim then add space

[snowman1976]

hello
I have some external data that I import daily into a spreadsheet using VBA. The data I am given is not consistent - there are often spaces in the string in data in column 'I', and I would like to clean it up before importing. The spacing is also never consistent so I would like to make it uniform before importing.

is there a way in VBA to change any spacing found in column I to 4 spaces? I have figured out a way already, but its quite slow. Basically I am trimming out the column first, then spreading it out to 4 spaces....but its very slow.

any help is appreciated


current code after opening the csv file:


[I:I] = [IFERROR(TRIM(I:I),"""")]
Columns("I:I").Select
Selection.Replace What:=" ", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

 

[Fluff]

Try

With Intersect(ActiveSheet.UsedRange, Range("I:I"))
   .Value = Evaluate("if({1},trim(" & .Address & "),"""")")
   .Replace " ", "     ", xlPart, , , , False, False
End With