How to separate comma separated values (csv)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 rowindex or columnindex 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 leftover 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 
