Thread: Parsing Data Strings into Columns

I have a spreadsheet that gathers information from multiple cells and strings the data into a long string - each piece of data broken by a comma in the string.

In another spreadsheet, I copy this data string into column A on sheet 1 - starting on row 2 and down.

On sheet 2 - and this is where I need help - I would like to have a macro that will take each data string on Sheet 1 column A and parse it out into multiple columns but the same row on Sheet 2.

ColumnA Row2 Sheet1 -> changing to many columns on Sheet2 but still on the same row.

The data string always represents the same number of data components - its just a matter of breaking it out and placing each data piece into its own column.

Thanks in advance.

Hello GuruWannaB,

If you have a workbook you can post, it will make answering your questions faster and easier.

Hello GuruWannaB,

I take it the data will start in row 1 of Sheet1. Do you want to format the date a specific way or just use the system default?

Sub x()
    Dim astr() As String
    Dim cell As Range
    
    With Worksheets("Sheet1")
        For Each cell In .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
            astr = Split(cell.Text, ",")
            Worksheets("Sheet2").Cells(cell.Row, "A").Resize(, UBound(astr) + 1) = astr
        Next cell
    End With
End Sub

Oops. Try again.

Hello GuruWannaB,

This will convert Sheet1's data into separate columns on Sheet2.

Sub ConvertToColumns()

  Dim DstRng As Range
  Dim LastRow As Variant
  Dim R As Long
  Dim SrcRng As Range
  
    Set SrcRng = Worksheets("Sheet1").Range("A2")
      LastRow = SrcRng.Parent.Cells(Rows.Count, "A").End(xlUp).Row
      LastRow = IIf(LastRow < SrcRng.Row, SrcRng.Row, LastRow)
      Set SrcRng = SrcRng.Parent.Range("A2:A" & LastRow)
      
    Set DstRng = Worksheets("Sheet2").Range("A2")
    Set DstRng = DstRng.Resize(SrcRng.Rows.Count, 1)
    SrcRng.Copy Destination:=DstRng
    
    DstRng.Parent.Activate
    DstRng.TextToColumns Comma:=True
    
End Sub