Removing every second instance of comma sign

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

Thanks Jim, that worked great!