TRIM in VBA with Substitute

[jam1531]

Hello,

I have searched and t this point have not found anything that is very helpful with the substitute function in VBA. I am unsure why but sometimes TRIM does not remove the space after a the word in a cell.

Someone gave me the following which seems to work

=TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32)))

However I want to create a personal macro that will run this in each cell that is currently selected. For some reason the below code I have built out so far does not work. What am I missing or is there an easier way altogether? I believe the problem occurs with the substitute. Any help appreciated.

Sub tested()

Dim cell As Range
For Each cell In Selection


Selection.Value = TRIM(Substitute(Selection.Value, Chr(160), Chr(32)))


Next


End Sub

 

[Norie]

VBA doesn't have a SUBSTITUTE function, try using Replace instead.

Sub tested()
Dim cell As Range

    For Each cell In Selection
        Cell.Value = Trim(Replace(Cell.Value, Chr(160), Chr(32)))
    Next

End Sub

 

[jam1531]

PERFECT! and good to know. Thanks!

 

[Fluff]

Another thing to note, unlike the Xl function, the VBA trim does not remove multiple spaces between words, so you may want

Cell.Value = Application.Trim(Replace(Cell.Value, Chr(160), Chr(32)))