Text to Column change as Text to row

Question

Have some new data to workout with Text to rows format.

In normal we will use text delimiter in excel to split the data from one column. I am in need of the same feature in row wise.

example List of location in a column.

CHARLESTON, JACKSONVILLE, BALTIMORE, NEW YORK, NORFOLK, SAVANNAH
CHARLESTON, JACKSONVILLE, BALTIMORE, NEW YORK, NORFOLK, SAVANNAH
HOUSTON, MOBILE, NEW ORLEANS
HOUSTON, MOBILE, NEW ORLEANS
PORT EVERGLADES

Expected Result :

Source Data Expected result

 CHARLESTON
 JACKSONVILLE
 BALTIMORE
 NEW YORK
 NORFOLK
 SAVANNAH

Kindly help me with a vba code for the same.

Thanks Sample

Sample file updated :

Excel

You could just split those rows into an array and print them to the worksheet. Run it in a loop to print each row in its own column. — Brian, Nov 28 '16 at 16:33
Or you could just use `Text to Columns` as usual then copy the range and `Paste Special` >> `Transpose` to avoid VBA altogether. :-) — Brian, Nov 28 '16 at 16:42
Instructions on unpivot: http://superuser.com/questions/78439/is-it-possible-to-unpivot-or-reverse-pivot-in-excel — xQbert, Nov 28 '16 at 16:44

score 0 · Answer 1 · answered Nov 28 '16 at 16:56

0

you can use this Sub:

Sub TextToRows(txt As String, targetRng As Range)
    Dim arr As Variant

    arr = Split(Replace(txt, " ", ""), ",")
    targetRng.Resize(UBound(arr) + 1).Value = Application.Transpose(arr)
End Sub

to be called from your main code as follows:

TextToRows Range("A1").Text, Range("B1") '<--| write the content of cell "A1" from cell "B1" downwards

answered Nov 28 '16 at 16:56

user3598756

28,893
4
18
28

Thanks for your input........ Getting near as needed. Updated the source data and result reported... please help – Mike Nov 28 '16 at 17:31
I gave you the code to fulfill the requested _"Text to Column change as Text to row"_ you asked for and as per your linked example, too. You may then want to accept my answer. While for further issues you should made a new post showing your coding efforts – user3598756 Nov 28 '16 at 17:48

Brian · Answer 2 · 2016-11-28T18:20:44.697

Try this:

Option Explicit

Public Sub TextToRows(MyRange As Range)

Dim MyArray As Variant
Dim r As Range
Dim i As Integer

i = 0
For Each r In MyRange
    MyArray = Split(WorksheetFunction.Trim(r), ", ")
    Range("B1").Offset(0, i).Resize(UBound(MyArray) + 1, 1).Value = Application.Transpose(MyArray)
    i = i + 1
Next r

End Sub

Sub RunTest()
    TextToRows Range("A1:A5")
End Sub

It only works for data in the first five rows of column A and prints to columns B and beyond. But you can modify it. :-)

Text to Column change as Text to row

2 Answers2