Trying to sort a text string by stepping through its components

[Izran]

Hey guys,


Lucky me!! Someone gave me a text file with multiple data inputs on
each line. I copied the file into excel and I'm trying to sort out the

1st, 2nd, 6th and 9th piece of data for later use. Somehow I am
messing up stepping through the string. I am trying to use the commas
in each line to separate the pieces of data.


Here is an example of some of the lines to be sorted:


5,1813,1700,1720,0,9732,9813,9868,0


10,1809,1763,1740,0,9734,9818,9866,1


15,1806,1757,1726,0,9729,9818,9878,1


20,1801,1744,1760,0,9722,9818,9878,2


I copied this data into column A would liek to place the selected
sorted data in columns BCD & E.


Here is the code I've developed so far (I know some of you may know how

to do this in 2 steps, but the only programming courses I've take were
in C):


Sub Sort()


Dim sMSRData As String
Dim sCurrent As String
Dim sVoltage As String
Dim iString As Integer
Dim sCycle As String
Dim sThermal As String
Dim iRow As Integer
Dim iCommaCount As Integer
Dim iTo As Integer


iRow = 2
iString = 0
iCommaCount = 0
iTo = 0


For iRow = 2 To 3000


iString = 0
iTo = 0


If IsEmpty(Cells(iRow, 1).Value) Then


iRow = 3000


End If


If Not IsEmpty(Cells(iRow, 1).Value) Then


sMSRData = Cells(iRow, 1)
iCommaCount = 0


For iString = 0 To 255


If iCommaCount = 0 Then


iTo = 0


If sMSRData(iString).Value <> "," Then


sCycle(iTo) = sMSRData(iString)


End If


If sMSRData(iString).Value = "," Then


iCommaCount = iCommaCount + 1


End If


End If


If iCommaCount = 1 Then


iTo = 0


If sMSRData(iString).Value <> "," Then


sCurrent(iTo) = sMSRData(iString)


End If


If sMSRData(iString).Value = "," Then


iCommaCount = iCommaCount + 1


End If


End If


If iCommaCount < 5 Then


If iCommaCount >= 2 Then


If sMSRData(iString).Value = "," Then


iCommaCount = iCommaCount + 1


End If


End If


End If


If iCommaCount = 5 Then


iTo = 0


If sMSRData(iString).Value <> "," Then


sVoltage(iTo) = sMSRData(iString)


End If


If sMSRData(iString).Value = "," Then


iCommaCount = iCommaCount + 1


End If


End If


If iCommaCount < 8 Then


If iCommaCount >= 6 Then


If sMSRData(iString).Value = "," Then


iCommaCount = iCommaCount + 1


End If


End If


End If


If iCommaCount = 8 Then


iTo = 0


If sMSRData(iString).Value <> "," Then


sThermal(iTo) = sMSRData(iString)


End If


If sMSRData(iString).Value = "" Then


iString = 255


End If


End If


If iString = 255 Then


Cells(iRow, 2) = sCycle
Cells(iRow, 3) = sCurrent
Cells(iRow, 4) = sVoltage
Cells(iRow, 5) = sThermal


End If


iTo = iTo + 1


Next iString


End If


Next iRow


End Sub


Thank you for any help you can give me,
MPManzi

 

[Alok]

How about using the Data/Text to columns option?

Alok

 

[Dave Peterson]

Maybe you could use Data|Text to columns to parse your input into 9 different
columns (or use skip for the fields you don't need).

 

[Tom Ogilvy]

An additional approach might be just to open the file in Excel using
File=>Open

 

[Ron Rosenfeld]

Hey guys,


Lucky me!! Someone gave me a text file with multiple data inputs on
each line. I copied the file into excel and I'm trying to sort out the

1st, 2nd, 6th and 9th piece of data for later use. Somehow I am
messing up stepping through the string. I am trying to use the commas
in each line to separate the pieces of data.


Here is an example of some of the lines to be sorted:


5,1813,1700,1720,0,9732,9813,9868,0


10,1809,1763,1740,0,9734,9818,9866,1


15,1806,1757,1726,0,9729,9818,9878,1


20,1801,1744,1760,0,9722,9818,9878,2


I copied this data into column A would liek to place the selected
sorted data in columns BCD & E.

VBA has a SPLIT function that might do what you want. Here is a suggestion:
(Note that the SPLIT function returns a zero-based array)

============================
Option Explicit

Sub Spl()
Dim DataArray As Variant
Dim RowNum As Long

For RowNum = 1 To 3000
With Cells(RowNum, 1)
'Is it valid data -- 8 commas
If Len(.Text) - Len(Replace(.Text, ",", "")) = 8 Then
DataArray = Split(.Text, ",")

.Offset(0, 1).Value = DataArray(0)
.Offset(0, 2).Value = DataArray(1)
.Offset(0, 3).Value = DataArray(5)
.Offset(0, 4).Value = DataArray(8)

End If
End With
Next RowNum
========================


--ron