none
How to split 1 column into 3 without a delimiter? RRS feed

  • Question

  • I have a csv import into my excel doc. I have 1 column of data that comes from a csv. the csv splits each entry by a line break. the first 3 entries are supposed to be 1 row. so every 3 entries a new row should be made. 

    Im having a tough time with this one. would i use a combination of a transpose formula and something else? Would a vba code be required?

    Any help would be great. 


    Wednesday, January 25, 2017 8:19 PM

Answers

  • Oskar,

    You can do this with the next formula in C2: =INDEX($A:$A,3*(ROW()-1)-2+(COLUMN()-3),1)

    Fill this one to the right until E2.

    Then Fill the cells C2:E2 down as far as needed.

    Jan

    • Marked as answer by Oskar Plinski Friday, January 27, 2017 5:45 PM
    Wednesday, January 25, 2017 8:57 PM
  • Hi Oskar Plinski,

    you can do this with a simple VBA code like below.

    so for an example , I create a sheet like yours.

    you can see that data are looks like same as yours.

    then I use code below.

    Sub demo()
    Dim i As Integer
    Dim LastRowina, LastRowinc, LastRowind, LastRowine As Long
    With ActiveSheet
       LastRowina = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    For i = 1 To LastRowina Step 3
        With ActiveSheet
            LastRowinc = .Cells(.Rows.Count, "C").End(xlUp).Row + 1
            LastRowind = .Cells(.Rows.Count, "D").End(xlUp).Row + 1
            LastRowine = .Cells(.Rows.Count, "E").End(xlUp).Row + 1
        End With
            Cells(LastRowinc, "C").Value = Cells(i, 1).Value
            Cells(LastRowind, "D").Value = Cells(i, 1).Offset(1, 0).Value
            Cells(LastRowine, "E").Value = Cells(i, 1).Offset(2, 0).Value
    Next i
    End Sub

    Output:

    1. so here you just need to use a loop and increment it by 3.
    2. then use offset to get all values.
    3. then find the last cell in column and set the value to that cell.
    4. repeat the process till end of the data.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 26, 2017 5:46 AM
    Moderator

All replies

  • You can write a macro to loop through the column and store the data into the other rows.

    Wednesday, January 25, 2017 8:27 PM
  • Below is a sample marco which will split the columns.

    Sub Split()
       Dim SourceRowStart As Integer
       Dim SourceRowEnd As Integer
       Dim SourceWorkSheet As Worksheet
       Dim DestWorkSheet As Worksheet
       Dim DestWorkSheetName As String
       Dim SourceWorksheetName As String
       Dim SourceRange As String
       Dim DestRange As String
       Dim DestRowCnt As Integer
       
       SourceWorksheetName = "Sheet1"
       DestWorkSheetName = "Sheet1"
       
       Set SourceWorkSheet = Worksheets.Item(SourceWorksheetName)
       Set DestWorkSheet = Worksheets.Item(DestWorkSheetName)
       SourceRowStart = 1
       SourceRowEnd = 12
       DestRowStart = 1
       For x = SourceRowStart To SourceRowEnd Step 3
            DestRowCnt = DestRowCnt + 1
            
            SourceRange = "A" & Trim(Str(x))
            
            DestRange = "B" & Trim(Str(DestRowCnt))
            DestWorkSheet.Range(DestRange).Value = SourceWorkSheet.Range(SourceRange).Value
            
            SourceRange = "A" & Trim(Str(x + 1))
                    DestRange = "C" & Trim(Str(DestRowCnt))
            DestWorkSheet.Range(DestRange).Value = SourceWorkSheet.Range(SourceRange).Value
            
            SourceRange = "A" & Trim(Str(x + 2))
            DestRange = "D" & Trim(Str(DestRowCnt))
            DestWorkSheet.Range(DestRange).Value = SourceWorkSheet.Range(SourceRange).Value
            
       Next
    End Sub

    Wednesday, January 25, 2017 8:42 PM
  • You can add two columns, one that identifies each set of three rows as a tuple, and another that categorizes each row by "WorkID", "URL", and "Name"; then use Power Query to pivot the Value(s) by Category.

    Tuple    Category    Value
    1    Work ID    WO3596
    1    URL    http://someurl1
    1    Name    Some-Name
    2    Work ID    WO3597
    2    URL    http://someurl2
    2    Name    Some-Name
    3    Work ID    WO3598
    3    URL    http://someurl3
    3    Name    Some-Name


    Jason

    Wednesday, January 25, 2017 8:46 PM
  • Oskar,

    You can do this with the next formula in C2: =INDEX($A:$A,3*(ROW()-1)-2+(COLUMN()-3),1)

    Fill this one to the right until E2.

    Then Fill the cells C2:E2 down as far as needed.

    Jan

    • Marked as answer by Oskar Plinski Friday, January 27, 2017 5:45 PM
    Wednesday, January 25, 2017 8:57 PM
  • Hi Oskar Plinski,

    you can do this with a simple VBA code like below.

    so for an example , I create a sheet like yours.

    you can see that data are looks like same as yours.

    then I use code below.

    Sub demo()
    Dim i As Integer
    Dim LastRowina, LastRowinc, LastRowind, LastRowine As Long
    With ActiveSheet
       LastRowina = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    For i = 1 To LastRowina Step 3
        With ActiveSheet
            LastRowinc = .Cells(.Rows.Count, "C").End(xlUp).Row + 1
            LastRowind = .Cells(.Rows.Count, "D").End(xlUp).Row + 1
            LastRowine = .Cells(.Rows.Count, "E").End(xlUp).Row + 1
        End With
            Cells(LastRowinc, "C").Value = Cells(i, 1).Value
            Cells(LastRowind, "D").Value = Cells(i, 1).Offset(1, 0).Value
            Cells(LastRowine, "E").Value = Cells(i, 1).Offset(2, 0).Value
    Next i
    End Sub

    Output:

    1. so here you just need to use a loop and increment it by 3.
    2. then use offset to get all values.
    3. then find the last cell in column and set the value to that cell.
    4. repeat the process till end of the data.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 26, 2017 5:46 AM
    Moderator
  • Could this be re-scripted to a google sheet script? 
    Friday, January 27, 2017 5:43 PM
  • Wow.. that worked flawlessly 

    Thank you!

    Friday, January 27, 2017 5:45 PM
  • Hi Oskar Plinski,

    you had mentioned that,"Could this be re-scripted to a google sheet script? "

    do you mean that you want a similar code like above that can run in google script.

    if yes, then you need to convert it because we don't have information how google script works.

    you can use the same logic just need to change the syntax.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 30, 2017 7:02 AM
    Moderator