Help with Data Import Problem

[purceld2]

I have a data import problem. I think it is because the import data is just one long string of data without any tabs new line etc.

How can I get the data below (Example 1) which is in a text file to look like (Example 2)

Example 1
40693,1423,137,40694,1897,137,40695,1896,137,40696,2069,137,40697,1939,137,40700,2092,137,40701,2017,137,40702,1909,137,40703,1978,137,40704,1917,137,40707,1942,137,

Example 2 (Without Comma each value in a cell)
Row 1
40693,1423,137,
Row 2
40694,1897,137,
Row 3
40695,1896,137,

Etc

Thank you in advance for any assistance

 

[Silvrhelm]

Hi, please copy the full text string into cell A1

Then run this macro

Sub org()
Dim rng As String
Dim x As String
Dim y As String
Dim z As String
Dim i As Integer
    rng = Range("a1")
        x = Len(rng)
            For i = 1 To (x / 15)
            y = Right(rng, (x - (15 * i)))
            z = Left(y, 15)
                Range("a1").Offset(i, 0) = z
            Next i
End Sub

when I ran this on your example it screwed up because there was a space after one of the commas.

If this is common throughout the string first run this formula on the text string in A1

ie in B1 =SUBSTITUTE(A1," ","")

Then copy the new text string without spaces back into A1

Also you say "without comma value in a cell"

Not sure exactly what you mean by this but if you just want the results to be like 40693 1423 137 with spaces instead of commas.

After the macro runs and you have the list from A2 down you can just run this formula for each cell

=substitute(A2,","," ")

Hope this helps, ill be happy to explain anything thats unclear.

 

[purceld2]

Thank you very much much appricated.

Just one thing. Is it posible to put he values in a separate cellsw please

i.e instead of being in one cell
40702,1909,137,

Have them in seperate cells
A2 B2 B3
40702 1909 137

 

[jimrward]

assuming your long string is in cell A1, and ends with comma

try the following code

Sub ss()

s = ActiveSheet.Range("A1").Value
s = Replace(s, ",137,", ",137~")

b = Split(s, "~")

intnumrows = 2
For j = 0 To UBound(b)
    d = Split(b(j), ",")
    For k = 0 To UBound(d)
        ActiveSheet.Range("A" & intnumrows).Offset(0, k).Value = d(k)
    Next k
    intnumrows = intnumrows + 1
Next j
End Sub

 

[purceld2]

Guys,

I think I may have confused you. The example I gave you probably was not idea.

1. The values in the string are not constant so they could change to any value.
2. The constant in the string is the commas. After every 3 commas a new line/row is required with the three values put into 3 separate cells removing the comma's

Sorry to have confused you.

Thanks again for your help

 

[jimrward]

simplifies it more try this, I have left test string in plus commented out bit to pick up data in A1

Sub ss()
s= "40693,1423,137,40694,1897,137,40695,1896,137,40696,2069,137,40697,1939,137,40700,2092,137,40701,2017,137,40702,1909,137,"

's = ActiveSheet.Range("A1").Value

b = Split(s, ",")

intnumrows = 2
For j = 0 To UBound(b) - 1 Step 3
        ActiveSheet.Range("A" & intnumrows).Offset(0, 0).Value = b(j)
        ActiveSheet.Range("A" & intnumrows).Offset(0, 1).Value = b(j + 1)
        ActiveSheet.Range("A" & intnumrows).Offset(0, 2).Value = b(j + 2)
        intnumrows = intnumrows + 1
Next j
End Sub

 

[Silvrhelm]

Hi I think this might be easier if you select your data and go to Data > Text to columns

Then choose delimited and seperate by comma, you now get a list of all the numbers in different cells with no commas.

Now if you still need to get them in like rows of 3 you can use this macro (with numbers starting in A1 and having A1 selected when you run it)

sub macrotime()
Dim i As Integer
    For i = 1 To 30
        ActiveCell.Offset(0, 3).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Cut
        Range("A1").Offset(i, 0).Select
        ActiveSheet.Paste
    Next i
End Sub

 

[purceld2]

Jimrward

Thanks you very much it works perfectly.

Regards

Desmond

Thanks all for your help much appreciated

 

[jimrward]

could possibly add in couple extra lines to read line from external text file and process accordingly, as sometimes excel truncates the cell contents to 255 chars