How can I list horizontal data vertically without using transpose?

Question

I'm given an exported spreadsheet that has data in a single cell, separated by "ALT + ENTER". I want this data to be on each line vertically instead of in a single cell.

I tried to delimit these by "CTRL + J" into columns. I thought maybe this would be easier to list out vertically (row by row). You can see where I'm trying to get at

I tried some VBA that I found online, but I don't know much of anything about it. If I were trying to figure out a VBA solution to this, it would take me even longer because I don't know a lot.

I tried this:
Sub vertsplit() Dim xRg As Range Dim xOutRg As Range Dim xCell As Range Dim xTxt As String Dim xStr As String Dim xOutArr As Variant On Error Resume Next xTxt = ActiveWindow.RangeSelection.Address Set xRg = Application.InputBox("please select the data range:", "Kutools for Excel", xTxt, , , , , 8) If xRg Is Nothing Then Exit Sub Set xOutRg = Application.InputBox("please select output cell:", "Kutools for Excel", , , , , , 8) If xOutRg Is Nothing Then Exit Sub For Each xCell In xRg If xStr = "" Then xStr = xCell.Value Else xStr = xStr & "," & xCell.Value End If Next xOutArr = VBA.Split(xStr, ",") xOutRg.Range("A1").Resize(UBound(xOutArr) + 1, 1) = Application.WorksheetFunction.Transpose(xOutArr) End Sub

I replaced the "ALT + ENTERS" with commas and then tried the code above, but it doesn't work well for multiple cells.

FUNCTION SOLUTION ERROR

You'll probably need VBA for this - what have you tried so far? You can likely just split the cells, save to an array, then output to a new range. — BruceWayne, Aug 15 '19 at 17:47
I'm using Microsoft Office Standard 2019 64-bit @ScottCraner — hanbanan, Aug 15 '19 at 20:22
@BruceWayne I just edited my main post that shows the code I have tried. I can split them into columns. How would I save it to an array in VBA and output them in a single column? — hanbanan, Aug 15 '19 at 20:26

score 2 · Accepted Answer · answered Aug 15 '19 at 18:09

2

If you have Office 365 Excel, one can use TEXTJOIN to create one line of text and parse that:

=TRIM(MID(SUBSTITUTE(TEXTJOIN(CHAR(10),TRUE,$A$1:$A$4),CHAR(10),REPT(" ",999)),(ROW(1:1)-1)*999+1,999))

answered Aug 15 '19 at 18:09

Scott Craner

148,073
10
49
81

I don't understand the function, but it resolved my issue somewhat. How do I increase the range from $A$1:$A$4 to a higher number? – hanbanan Aug 15 '19 at 21:16
Just change the `4` it to the row you want to end on. ultimately if you want to put the output in a different column then you could just use `A:A`. – Scott Craner Aug 15 '19 at 21:18
Thanks for this, by the way. It's very useful. When I change the range to anything more than $A:$10, all I get is a value error. I'll post another image in the main post showing what the issue is. – hanbanan Aug 15 '19 at 21:43
@hanbanan something is not right. in your photo the `ROW(1:1)` should be `ROW(6:6)` By the time it got dragged down there. Try deleting all the outputs and put the formula new in the first cell and drag it down. – Scott Craner Aug 15 '19 at 22:05

score 1 · Answer 2 · answered Aug 15 '19 at 20:58

1

Excel 2010+ you can use Power Query aka Get & Transform

Select a cell in the column
Get the data from the table/range
Split by the #lf into rows

answered Aug 15 '19 at 20:58

Ron Rosenfeld

53,870
7
28
60

How can I list horizontal data vertically without using transpose?

2 Answers2