How to separate comma separated values (csv) in a worksheet cell in Microsoft Excel into rows or columns |
||||
| Excel 2007 | Excel 2003 | Macros Excel 2007 | Macros Excel 2003 | Excel 2010 |
|
|
||||
|
|
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 (about 15 MB)below to see how you can use the macro to separate the cell data (csv values) into rows and columns: More Tips: How do I view and edit Microsoft Excel files on an iPad? Inventory Soluton How to rename and color worksheet tabs Copy Worksheet Quickly Criteria Range Animations in Microsoft Excel Create two charts at once How to avoid errors while working in Excel How to benefit from Microsoft Excel Templates How to use Microsoft Excel 2007 with Word 2007 - create mail merge labels Landscape Oriented Worksheet Template How to-make a worksheet fit a printed page How to make your charts more impressive Perpetual Calendar from 1900 to 9999 How to create your own custom add-in How to clear conditional formattng in Excel worksheets How to speed up data entry of decimal numbers How to change the color of the gridlines of an Excel worksheet or hide them How to use Autosum in Multiple Worksheet Cells Quickly How to have your free personal assistant in Mcrosoft Excel who reads out the data to you How to use the status bar in Microsoft Excel to do a quick data analysis How to calculate equal monthly payments or instalments using a mathematical equation Data Forms to Enter and Edit Data View 2 worksheets in same workbook side by side Accessing a specific worksheet in large workbooks with multiple worksheets Referencing a cell in another worksheet How to perform a what-if analysis using a scrollbar form control An interesting payroll solution Future Value Solution in Microsoft Excel Another Interesting Conditional Formatting Solution DSUM and Array Formulas for addition solutions IF Function Question How to use command buttons on a splash screen in MS-Excel Copy Paste Data from one Excel Worksheet to Another |
|||
|
|
||||
| Excel 2007 | Excel 2003 | Macros Excel 2007 | Macros Excel 2003 | Excel 2010 |
|
|
||||