# How to split 1 column into 3 without a delimiter?

• ### 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

• 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 Friday, January 27, 2017 5:45 PM
Wednesday, January 25, 2017 8:57 PM
• 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

### 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 Friday, January 27, 2017 5:45 PM
Wednesday, January 25, 2017 8:57 PM
• 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
• 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
• 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