I have a 1542 rows in a single column.

I would like to break this up into multiple columns with the same # or close to same number of rows in each column. Also if possible be able to do this different ways...

i.e. break it up into 4,5,6 or more columns my data is IP addresses so it would be text in each cell below is sample of my data

A simple vba script to take the value in a selected cell and break it into separate cells is:

Sub Macro1()
    Dim rCell As Range, cString As String, cr As String
    Set rCell = Selection(1)
    a = rCell.Value
    cr = Chr(13)
    If InStr(a, cr) > 0 Then
        ary = Split(a, cr)
        For i = 1 To UBound(ary)
            rCell.Offset(1, 0).EntireRow.Insert
        Next i
        For i = 0 To UBound(ary)
            rCell.Offset(i, 0).Value = ary(i)
        Next i
    End If

End Sub

This macro finds char(13), carriage return, and adds new cell based on finding it. If this does not work you may have char(10), line feeds, so just change the number in the macro.

This macro is plagiarized in part from other posts both on this and other forums


Give this a try:

Sub marine()
    Dim i As Long, j As Long, k As Long
    Dim v As String, Old As String
    Dim N As Long

    N = Cells(Rows.Count, "A").End(xlUp).Row
    j = 2
    i = 1
    Cells(1, 2) = Cells(1, 1)
    Old = Split(Cells(i, 1), ".")(1)

    For k = 2 To N
        v = Cells(k, 1).Value
        If Split(v, ".")(1) <> Old Then
            j = j + 1
            i = 1
            Old = Split(v, ".")(1)
            i = i + 1
        End If
        Cells(i, j) = v
    Next k
End Sub

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.