Transposing Horizontal String

[c0087]

A	B
12 13 14 2 1	12
9 6 5 14 3	13
1 3 15 2 6	14
2 4 6 10 12	2
	1
	9
	6
	5
	14
	3

I need to transpose the horizontal string in column A to look like the example in column B
Every string in column A has the same number ( in this case 5 different numbers)

 

[Fluff]

How about

Fluff.xlsm
	A	B
1
2	12 13 14 2 1	12
3	9 6 5 14 3	13
4	1 3 15 2 6	14
5	2 4 6 10 12	2
6		1
7		9
8		6
9		5
10		14
11		3
12		1
13		3
14		15
15		2
16		6
17		2
18		4
19		6
20		10
21		12
Data

Cell Formulas
Range	Formula
B2:B21	B2	=TEXTSPLIT(TEXTJOIN(" ",,A2:A5),," ")
Dynamic array formulas.

 

[c0087]

How about

Fluff.xlsm
	A	B
1
2	12 13 14 2 1	12
3	9 6 5 14 3	13
4	1 3 15 2 6	14
5	2 4 6 10 12	2
6		1
7		9
8		6
9		5
10		14
11		3
12		1
13		3
14		15
15		2
16		6
17		2
18		4
19		6
20		10
21		12
Data

Cell Formulas
Range	Formula
B2:B21	B2	=TEXTSPLIT(TEXTJOIN(" ",,A2:A5),," ")
Dynamic array formulas.

Yes, but I just need a space in between each set of 5 (it shrunk it in the example)

 

[mumps]

Try:

Sub TransposeData()
    Application.ScreenUpdating = False
    Dim v As Variant, vCell As Variant, arr() As Variant, i As Long, ii As Long, x As Long
    v = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
    For i = LBound(v) To UBound(v)
        vCell = Split(v(i, 1), " ")
        For ii = LBound(vCell) To UBound(vCell)
            x = x + 1
            ReDim Preserve arr(1 To x)
            arr(x) = vCell(ii)
        Next ii
    Next i
    Range("B1").Resize(x).Value = Application.Transpose(arr)
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

but I just need a space in between each set of 5

In that case

Fluff.xlsm
	A	B
1
2	12 13 14 2 1	12
3	9 6 5 14 3	13
4	1 3 15 2 6	14
5	2 4 6 10 12	2
6		1
7
8		9
9		6
10		5
11		14
12		3
13
14		1
15		3
16		15
17		2
18		6
19
20		2
21		4
22		6
23		10
24		12
25
26
Data

Cell Formulas
Range	Formula
B2:B25	B2	=TEXTSPLIT(TEXTJOIN(" ",,A2:A5&" "),," ")
Dynamic array formulas.

 

[mumps]

VBA approach:

Sub TransposeData()
    Application.ScreenUpdating = False
    Dim v As Variant, vCell As Variant, arr() As Variant, i As Long, ii As Long, x As Long
    v = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
    For i = LBound(v) To UBound(v)
        vCell = Split(v(i, 1), " ")
        For ii = LBound(vCell) To 4
            x = x + 1
            ReDim Preserve arr(1 To x)
            arr(x) = vCell(ii)
            If ii = 4 Then
                ReDim Preserve arr(1 To x + 1)
                arr(x + 1) = ""
                x = x + 1
            End If
        Next ii
    Next i
    Range("B1").Resize(x).Value = Application.Transpose(arr)
    Application.ScreenUpdating = True
End Sub

 

[c0087]

VBA approach:

Sub TransposeData()
    Application.ScreenUpdating = False
    Dim v As Variant, vCell As Variant, arr() As Variant, i As Long, ii As Long, x As Long
    v = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
    For i = LBound(v) To UBound(v)
        vCell = Split(v(i, 1), " ")
        For ii = LBound(vCell) To 4
            x = x + 1
            ReDim Preserve arr(1 To x)
            arr(x) = vCell(ii)
            If ii = 4 Then
                ReDim Preserve arr(1 To x + 1)
                arr(x + 1) = ""
                x = x + 1
            End If
        Next ii
    Next i
    Range("B1").Resize(x).Value = Application.Transpose(arr)
    Application.ScreenUpdating = True
End Sub

This one also works if wanting to use VBA