CODEWITHSUNDEEP
excelvbaexcel-formula
hanbanan
hanbanan

Reputation: 81

How can I list horizontal data vertically without using transpose?

Excel picture here explains this best.

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

here

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

Upvotes: 1

Views: 584

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60354

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

enter image description here

enter image description here

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152585

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))

enter image description here

Upvotes: 2

Related Questions