Split Text in Rows

[IoanZ]

Hello to everyone.

I use this code to split text in to separate colums

Row/column	A	B	C	D	E	F	G
1	Split text in rows
2		Split	text	in	rows
3
4

<tbody>
</tbody>

Sub SplitTextColumn()
Dim i As Long
Dim vA As Variant
For i = 1 To Range("A1").Rows.Count
vA = Split(Range("A1").Resize(1).Offset(i - 1), " ")
Range("B2").Resize(1, UBound(vA) + 1) = vA
Next
End Sub

I want to know which is vba modification to split the text from Cell A1 into separate rows?

Thank's a lot

 

[footoo]

Range("B2").Resize(UBound(vA) + 1, 1) = Application.Transpose(vA)

 

[Rick Rothstein]

Hello to everyone.

I use this code to split text in to separate colums

Row/column	A	B	C	D	E	F	G
1	Split text in rows
2		Split	text	in	rows
3
4

<tbody>
</tbody>

Sub SplitTextColumn()
Dim i As Long
Dim vA As Variant
For i = 1 To Range("A1").Rows.Count
vA = Split(Range("A1").Resize(1).Offset(i - 1), " ")
Range("B2").Resize(1, UBound(vA) + 1) = vA
Next
End Sub

I want to know which is vba modification to split the text from Cell A1 into separate rows?

Thank's a lot

This is why single examples are not much help. Do you have data in cells A2, A3, etc.? While your loop's upper limit evaluates to 1 (which means only cell A1 is being processed), your use of a loop kind of suggests you have other data. So, do you? If so, how did you want the output to run downward... cell A1's split apart text followed by cell A2's split apart text etc.? Please clarify how much data you have and how it is laid out.

 

[IoanZ]

Row/Col	A	B	C	D	E
1	Split text in rows
2		Split
3		text
4		in
5		rows

<tbody>
</tbody>

I want to obtain a vertical split of the text.

Works perfectly with

Sub SplitTextColumn()
Dim i As Long
Dim vA As Variant
For i = 1 To Range("A1").Rows.Count
vA = Split(Range("A1").Resize(1).Offset(i - 1), " ")
Range("B2").Resize(1, UBound(vA)+1,1) = Application.Transpose(vA)
Next
End Sub

Thank's a lot guys

 

[alansidman]

Using Power Query, here is an alternate solution:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    SplitColumn = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1")
in
    SplitColumn

Steps taken in above Mcode
1. Import Table to PQ
2. Select Split Column
3. Select Rows
4. Close and Load to Excel

PQ is available in all versions of Excel beginning with 2010.

 

[Rick Rothstein]

And here is an alternate VBA solution that does not use any loops...

Sub SplitTextDownward()
  Dim Arr As Variant
  Arr = Application.Transpose(Split(Range("A1").Value))
  Range("B2").Resize(UBound(Arr)) = Arr
End Sub

Note: You can change the location of the output by changing the red highlighted B2 to a cell address of your own choosing (even including A1 if you would like to overwrite your original data).