excel - Split one row into several rows

Question

I have one row that contains 5000 cells and I want to split this data into several rows (4 cells per row).

Is there an automated way to do it?

Example: From this 123412341234

To this
1234
1234
1234

Program used is Excel

regards

Answer 1

This code will work if you have 5000 cells in a row and you want to split at every 4 cells and output in a column.

Sub splitInCols()
Dim inputRow As Long, nCol As Long, lastCol As Long, iRow As Long
Dim sht As Worksheet
Set sht = ActiveSheet

inputRow = 1 'Put your row number here
outputCol = 1 'The column where it will output
lastCol = sht.Cells(inputRow, sht.Columns.Count).End(xlToLeft).Column

iRow = 2    'Row start of your output
n = 0
For j = 1 To lastCol
    If n < 4 Then
        splitText = splitText & Cells(inputRow, j).Value
        n = n + 1
    End If
    If n = 4 Or j = lastCol Then
        Cells(iRow, outputCol).Value = splitText
        iRow = iRow + 1
        n = 0
        splitText = ""
    End If
Next
End Sub