# Split data insides a Cell

#### techissue2008

##### Board Regular
Hi
I use Excel 2002.

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

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### schielrn

##### Well-known Member
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 ":"?

Last edited:

#### jindon

##### MrExcel MVP
Hi
I use Excel 2002.

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
Code:
``````Sub test()
Dim r As Range
With Sheets("Sheet1")
With .Range("a1", .Range("a" & Rows.Count).End(xlUp))
For Each r In .Cells
Next
End With
End With
End Sub``````

Hi Jindon,

Tommy

#### jindon

##### MrExcel MVP
Stop Muliti/Cross poting
Code:
``````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
Next
End With
End With
End Sub``````

#### jindon

##### MrExcel MVP
Hi Jindon

Sorry, I Will Just Post On This Forum.

May I Ask Why There Are (0) And (1) In The End Of
Code:
``````Sheets("sheet7").range(r.offset(,2).address).value = Split(r.text, ":0")(0)
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.

Replies
10
Views
109
Replies
5
Views
62
Replies
3
Views
131
Replies
4
Views
153
Replies
9
Views
135

1,191,609
Messages
5,987,648
Members
440,104
Latest member
thigarette

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back