February 8, 2012

Text to columns through code in excel

All the data are combined in one column. Each cell has multiple values  to separate into the neighboring columns, which are separated by commas.

 Sub BreakDownTextToColumns()  
 Dim strTextData As String  
 Dim i As Integer, x As Integer  
 Dim strSplitText$()  
 x = 1  
 Range("A1").Select ' cell above data to separate  
 Do Until ActiveCell.Offset(x, 0).Range("A1").Value = ""  
 strTextData = ActiveCell.Offset(x, 0).Range("A1").Value  
 strSplitText = Split(strTextData, ",") ' a comma is used as the signal to separate  
 For i = 0 To UBound(strSplitText)  
 ActiveCell.Offset(x, i).Range("A1").Value = (strSplitText(i))  
 Next i  
 x = x + 1  
 End Sub