One large column to many columns and rows in excel

Question

My data currently is 5000 rows of contact information from different people; however each person provided inconsistent amounts of information so the data currently looks like this: Example Image 1 (Note: Column 'A' doesn't exist, only an example of what the number represent for clarification purposes)

I separated each person by a semi-colon. How do I transform this one large column to several columns stacked on top of each other on another sheet similar to the photo below:

Ideal Results Sheet 1

What have you tried so far? There are similar questions around, and you don't likely need VBA. Please show any attempts or code you have. — BruceWayne, May 30 '17 at 21:03
If Column A doesn't exist, how do you expect to figure out if e.g. B7 is email or phone? — NetMage, May 30 '17 at 23:15

score 1 · Answer 1 · answered May 31 '17 at 09:33

Following is VBA solution for your problem.

Sub Demo()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim lastrow, i, rowNum, colnum As Integer
    Dim srcSht, destSht As Worksheet

    Set srcSht = ThisWorkbook.Sheets("Sheet3")
    Set destSht = ThisWorkbook.Sheets("Sheet5")

    lastrow = srcSht.Cells(Rows.Count, "B").End(xlUp).Row
    rowNum = 2
    colnum = 1

    For i = 1 To lastrow
        If srcSht.Cells(i, 2).Value = ";" Then
            rowNum = rowNum + 1
            colnum = 1
        Else
            destSht.Cells(rowNum, colnum).Value = srcSht.Cells(i, 2).Value
            colnum = colnum + 1
        End If
    Next i

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

One large column to many columns and rows in excel

1 Answers1