Remove spaces and replace with comma.

[abjac]

HI I have a csv file which seems a little cracy, and i would like a way to be able to work with it.
I need to remove the spaces and and properly also the special caracters and replace them with a comma.
And then save the csv file again. I have tried to record find replace, but it put allot of commas inside, i only need one between.
I found below code, which maybe can be usefull. it remove all spaces, so i need that one to be maybe able to put a comma between.

Please have a look
Put a test sheet here and some lines of the csv file is in sheet 2

Sincerely Abjac

Here is the code which remove all spaces.

Sub TrimBText()
' This module will trim extra spaces from BOTH SIDES.
    Dim MyCell As Range
    On Error Resume Next
        Selection.Cells.SpecialCells(xlCellTypeConstants, 23).Select
        For Each MyCell In Selection.Cells
            MyCell.Value = Trim(MyCell.Value)
        Next
    On Error GoTo 0
End Sub

[tehneXus]

Hi,

is this what you need?

Sub TrimBText()
    'Trim and replace spaces with commas, treat multiple spaces as 1
    Dim xlCell As Range
    For Each xlCell In Sheets(2).Cells.SpecialCells(xlCellTypeConstants)
        xlCell.Value = Trim(xlCell.Value)
        Do While InStr(xlCell.Value, "  ") > 0
            xlCell.Value = VBA.Replace(xlCell.Value, "  ", " ")
        Loop
        xlCell.Value = VBA.Replace(xlCell.Value, " ", ",")
    Next xlCell
End Sub

[abjac]

Hi TehneXus. This was exactly what i was loooking for. Its really perfect thanks

Have a nice evening

Sincerely

Abjac