Forum Discussion
Data organized in Columns instead of Rows - Excel
Hi,
In excel, I have a long list of data that I want to organized in 4 columns (First Name, Last Name, Phone and Email), the thing is that the data I have is in one column, so like in the image, the data that I have is organized downwards in a single column, as you can see in A1 is Kevin's first and last name, in A2, is Kevin's phone number and in cell A3, his email, the same for John and Angela, how can I place the information in 4 columns like it is in the table on the right?
Thanks!
An alternative with legacy Excel e.g. Excel 2013 could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
Another possible variation for modern Excel:
=LET( wrapArray, WRAPROWS(A1:A12,3), fullNames, TAKE(wrapArray,,1), HSTACK( TEXTBEFORE(fullNames," ",,,1), TEXTAFTER(fullNames," ",,,1), DROP(wrapArray,,1) ) )In C2:
=LET(list, A1:A9, n, COUNTA(list), names, CHOOSEROWS(list, SEQUENCE(n/3, ,1, 3)), first, TEXTBEFORE(names, " "), last, TEXTAFTER(names, " "), phone, CHOOSEROWS(list, SEQUENCE(n/3, , 2, 3)), email, CHOOSEROWS(list, SEQUENCE(n/3, , 3, 3)), HSTACK(first, last, phone, email))
Sub transform()
Dim i As Long, j As Long, k As Long
Dim MyArray() As String
Dim MyString As StringRange("D:G").Clear
i = Range("A" & Rows.Count).End(xlUp).Row
k = 1
For j = 1 To iSelect Case j Mod 3
Case Is = 1
MyString = Cells(j, 1).Value
MyArray = Split(MyString)
Cells(k, 4).Value = MyArray(0)
Cells(k, 5).Value = MyArray(1)Case Is = 2
Cells(k, 6).Value = Cells(j, 1).Value
Case Is = 0
Cells(k, 7).Value = Cells(j, 1).Value
k = k + 1
End Select
Next jEnd Sub
This code returns the intended result in my sheet.
