Separate CSV in Excel Worksheet Cell into Rows Columns

How to separate comma separated values (csv) in an Excel worksheet cell into rows or columns
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) <> “”
‘assign 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
‘finally empty the cell a1 so that you now know that all the comma separated data has been properly transferred to different cells
‘now end the program
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) = Mid(mydata, (mycomma + 1))
‘ go to the next row or column
y = y + 1
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:

One thought on “Separate CSV in Excel Worksheet Cell into Rows Columns

  1. Daniel Radebaugh

    This code does not work in 2010. Could you send me updated code for extracting CSV into single column cells.


Leave a Reply

Your email address will not be published. Required fields are marked *