CODEWITHSUNDEEP
excelvba
Rohit
Rohit

Reputation: 189

Replace comma(,) with dot(.) only at particular location in the string

I have string each on multiple line which looks like this

S087A1097,99,86,0,14,0,good
S087A1097,100,0,10,14,0,good
S087A1097,0,0,100,0,0,good

And I need to change it to this respectively.

S087A1097,99.86,0.14,0,good
S087A1097,100.0,10.14,0,good
S087A1097,0.0,100.0,0,good

How can I achieve this in Excel

Upvotes: 0

Views: 3384

Answers (2)

Shai Rado
Shai Rado

Reputation: 33692

If you want to use a VBA solution, you can try the code below.

It might seem a little long, but it's very fast to execute since there is little "messing" with the worksheet, and most of the logic is done on Arrays.

Code

Option Explicit

Sub ImportCsvContents()

Dim csvLines As Variant, CurrentRow As Variant
Dim LastRow As Long, i As Long

ReDim csvLines(0)

With Worksheets(1)
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    ' === logic, each order ends with data data in column "B" ===
    For i = 1 To LastRow
        csvLines(UBound(csvLines)) = .Range("A" & i).Value
        ReDim Preserve csvLines(UBound(csvLines) + 1) ' keep record and raise array index by 1
    Next i
End With

' resize array to actual populated size
If csvLines(UBound(csvLines)) = "" Then
    ReDim Preserve csvLines((UBound(csvLines) - 1))
End If


' loop through all lines in .csv file
For i = 0 To UBound(csvLines)
    CurrentRow = Split(csvLines(i), ",")

    CurrentRow(1) = CurrentRow(1) & "." & CurrentRow(2)
    CurrentRow(2) = CurrentRow(3) & "." & CurrentRow(4)
    CurrentRow(3) = CurrentRow(5)
    CurrentRow(4) = CurrentRow(6)

    ' re-format the current line
    csvLines(i) = CurrentRow(0) & "," & CurrentRow(1) & "," & CurrentRow(2) & "," & CurrentRow(3) & "," & CurrentRow(4)

    Erase CurrentRow ' clear array
Next i

' now just dump the entre array to the worksheet
Worksheets(1).Range("A1").Resize(UBound(csvLines) + 1).Value = Application.Transpose(csvLines)

End Sub

Upvotes: 0

Karl Kristjansson
Karl Kristjansson

Reputation: 328

if your text is in cell A1:

=SUBSTITUTE(SUBSTITUTE(A1,",",".",2),",",".",3)

Upvotes: 2

Related Questions