Split data insides a Cell

[techissue2008]

Hi
I use Excel 2002.

Spreadsheet 1 : Data source
Column One : Score
97:98
87:79

How can I get score data from column One of spreadsheet 1, Split the data 97:98 into 97 and 98 of spreadsheet 1, put them into 2 columns of spreadsheet 2

Column one
97
87
Column two
98
79

Sub splitdata()
Sheets("Sheet2").Cells.ClearContents
Sheets("Sheet2").Range("A").Value = Sheets("Sheet1").Range("A").Value
End Sub

It will show
0.041666667
0.001388889

 

[schielrn]

Welcome to the Board!

Try using data-->text to columns and use : as the delimiter.

Hope that helps.

Sorry I didn't see that you wanted to split them for another sheet.

Are they always 2 digits on both sides of the ":"?

 

[jindon]

Hi
I use Excel 2002.

Spreadsheet 1 : Data source
Column One : Score
97:98
87:79

How can I get score data from column One of spreadsheet 1, Split the data 97:98 into 97 and 98 of spreadsheet 1, put them into 2 columns of spreadsheet 2

Column one
97
87
Column two
98
79

Sub splitdata()
Sheets("Sheet2").Cells.ClearContents
Sheets("Sheet2").Range("A").Value = Sheets("Sheet1").Range("A").Value
End Sub

It will show
0.041666667
0.001388889

Are you sure ?
try

Sub test()
Dim r As Range
With Sheets("Sheet1")
    With .Range("a1", .Range("a" & Rows.Count).End(xlUp))
        For Each r In .Cells
            Sheets("sheet2").Range(r.Address).Resize(,2).Value = Split(r.Text, ":")
        Next
    End With
End With
End Sub

 

[techissue2008]

Hi Jindon,

Thanks for your help.

Tommy

 

[jindon]

Stop Muliti/Cross poting

Sub test()
Sheets("Sheet7").Cells.ClearContents
Dim r As Range
With Sheets("Sheet1")
    With .Range("c1", .Range("c" & Rows.Count).End(xlUp))
        For Each r In .Cells
            Sheets("sheet7").Range(r.Offset(,2).Address).Value = Split(r.Text, ":0")(0)
            Sheets("sheet7").Range(r.Offset(,5).Address).Value = Split(r.Text, ":0")(1)
        Next
    End With
End With
End Sub

 

[jindon]

Hi Jindon

Sorry, I Will Just Post On This Forum.

May I Ask Why There Are (0) And (1) In The End Of

Sheets("sheet7").range(r.offset(,2).address).value = Split(r.text, ":0")(0)
Sheets("sheet7").range(r.offset(,5).address).value = Split(r.text, ":0")(1)

after Adding Them, There Is No Small Green Triangle Shown In The Cell.

Tommy

1) Get vbHelp for Split function and when you still don't understand it, post back

2) Green triangle will appear the cell when the data format/formula is different from the other cell in the column.