# in an Excel worksheet cell into rows or columns

Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010

Home

Microsoft Excel Macros Question
Let's say cell A1 has " 10,15,20,25". I would like to cut the first numbers that ends before the ' , ' (comma) , then delete the comma and paste it into another cell. So in the end A1 has "15,20,25" and let's say the second cell was A2 and it now has "10". Is this possible and what is the code for it in VBA.

The complete code with the remarks using an apostrophe is given below:

Sub separatedata()
'define a variable that will hold the user cell data
Dim mydata As String
'define a variable that will hold the row-index or column-index values
y = 2
'start a loop and let it run as long as there is data in the cell A1
Do While Cells(1, 1) <> ""
'assgn the data in cell a1 to the variable my data
mydata = [a1]
'find out the position of the comma
mycomma = InStr(mydata, ",")
'to ensure that the data that is left-over does not have a comma is also worked upon by the program and shifted appropriately
if mycomma=0 then
' the end data, for example, 25 goes to the next empty row or column
cells(y,1)=[a1]
'finally empty the cell a1 so that you now know that all the comma separated data has been properly transferred to different cells
[a1]=""
'now end the program
end
end if
'[a2] = Left(mydata, (mycomma - 1)) - formula used without loop
'cells(y,2)= a2
'the left function extracts the number of characters from the left upto the comma and then removes the comma
Cells(y, 1) = Left(mydata, (mycomma - 1))
'the mid function extracts the characters after the comma in our case
'[a1] = Mid(mydata, (mycomma + 1)) - - formula used without loop
'cells(1,1)=a1
Cells(1, 1) = Mid(mydata, (mycomma + 1))
' go to the next row or column
y = y + 1
Loop
End Sub

Note: You can use the macro to separate all kinds of text including email addresses, etc.

Watch the video below to see how you can use the macro to separate the cell data (csv values) into rows and columns:

Home
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010