Removing every second instance of comma sign

[Triple7]

I need to import .csv files into excel. Problem is that the program
that exports the .csv files comma sepatates the values and the values
themselves contain commas. For example:

3,14,-1,28,0,74

Should be interpreted as:

3,14 -1,28 0,74

The only thing I have going for me is that all the values contain
commas. This means I should be able to replace every second comma with
another delimiter and thus be able to read the file correctly.
Something like this:

3,14;-1,28;0,74

I´ve been thinking about how to go about this but haven´t been able to
come up with anything that works. Does anyone have any suggestions on
how to replace every second comma in a string?

Regards / Thomas L

 

[Jim Cone]

Thomas,

This may do what you want...
'--------------------------------------------
Sub SecondCommasAreNotWorthy()
'Jim Cone - San Francisco, USA - Feb 11, 2005
'Replaces every second comma in every cell in
'the selection with a space.

Dim lngN As Long
Dim lngCount As Long
Dim strValue As String
Dim rngCell As Excel.Range

' The cells to change must be selected.
For Each rngCell In Selection
strValue = rngCell.Value
'Go thru each character in the cell
For lngN = 1 To Len(strValue)
'Identify commas
If Asc(Mid$(strValue, lngN, 1)) = 44 Then
lngCount = lngCount + 1
'Is the count of commas divisible by 2.
If lngCount Mod 2 = 0 Then
'Replace comma with space
Mid$(strValue, lngN, 1) = " "
End If
End If
Next 'lngN
'Replace text in the cell with new text.
rngCell.Value = strValue
lngCount = 0
'Now do it again
Next 'rngCell
Set rngCell = Nothing
End Sub
'-----------------------------------------------

Regards,
Jim Cone
San Francisco, USA

 

[Triple7]

Thanks Jim, that worked great!