Text to Columns Problem

[steve case]

I have 118 years of data all on one line that looks like this:

1900 6 3 1 1901 5 5 1 ... 2017 14 2 0 2018 8 6 2

It needs to be put into columns so it looks like this:

|1900| 6|3|1|
|1901| 5|5|1|
...
|2017|14|2|0|
|2018| 8|6|2|

So far my Excel skills have come up empty on this one. I have Excel 2007

 

[mikerickson]

Does TextToColumns with a space delimiter do what you want?

 

[steve the fish]

Is the data all in one cell or across the row?

 

[MickG]

If you data is in row 1 then perhaps this for results starting "A2".

[COLOR="Navy"]Sub[/COLOR] MG12Mar46
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Cells("1", Columns.Count).End(xlToLeft).Column
c = 1

[COLOR="Navy"]For[/COLOR] n = 1 To Lst [COLOR="Navy"]Step[/COLOR] 4
    c = c + 1
    Cells(c, 1).Resize(, 4).Value = Cells(1, n).Resize(, 4).Value
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[sandy666]

just out of curiosity...

like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]raw[/td][td][/td][td=bgcolor:#70AD47]Result.1[/td][td=bgcolor:#70AD47]Result.2[/td][td=bgcolor:#70AD47]Result.3[/td][td=bgcolor:#70AD47]Result.4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]1900 6 3 1 1901 5 5 1 2017 14 2 0 2018 8 6 2[/td][td][/td][td=bgcolor:#E2EFDA]1900[/td][td=bgcolor:#E2EFDA]6[/td][td=bgcolor:#E2EFDA]3[/td][td=bgcolor:#E2EFDA]1[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]1901[/td][td]5[/td][td]5[/td][td]1[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]2017[/td][td=bgcolor:#E2EFDA]14[/td][td=bgcolor:#E2EFDA]2[/td][td=bgcolor:#E2EFDA]0[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]2018[/td][td]8[/td][td]6[/td][td]2[/td][/tr]
[/table]

 

[steve case]

Does TextToColumns with a space delimiter do what you want?

Thanks for all the replies.

Yes, I used a space delimiter and then cut & pasted cut & pasted
cut & pasted cut & pasted cut & pasted etc. the 472 columns until
I got the four columns I wanted. Not what I thought would work
as "easy" as it turned out. I didn't count how many Cut & Pastes
it all was. I'm just glad it was only little over a century and not
several millennials (-:

On edit:

just out of curiosity...

Yup

If you data is in row 1 then perhaps this for results starting "A2".

Sub MG12Mar46
Dim Lst As Long, c As Long, n As Long
Lst = Cells("1", Columns.Count).End(xlToLeft).Column
c = 1


For n = 1 To Lst Step 4
    c = c + 1
    Cells(c, 1).Resize(, 4).Value = Cells(1, n).Resize(, 4).Value
Next n
End Sub

Regards Mick

I'm going to save the code for when it's mellinials

Is the data all in one cell or across the row?

One Cell

 

[steve the fish]

You can try this if code is ok. Data in cell A1 of activesheet. Results in A3:D3 downwards:

Dim arr, arr2(), a As Long, i as Long, j as Long

arr = Split(Range("A1").Value, " ")
ReDim arr2(0 To (UBound(arr)) / 4 - 1, 0 To 3)

For i = 0 To UBound(arr2, 1)
    For j = 0 To UBound(arr2, 2)
        arr2(i, j) = arr(a)
        a = a + 1
    Next
Next
Range("A3").Resize(UBound(arr2) + 1, UBound(arr2, 2) + 1) = arr2

 

[sandy666]

Yup

it's easy but you need upgrade your excel to (minium) one level higher
or
use vba from Mick

 

[jtakw]

Hi,

Wish I saw your post earlier, might have been able to save you the effort of the manual work you described...

If I understand correctly, here's 2 versions of a formula solution.

Use B1 formula if you want the data extracted Down column, formula copied down.
Use B8 formula if you want the data extracted Across columns, formula copied across.

Book1
	A	B	C	D	E
1	1900 6 3 1 1901 5 5 1 2017 14 2 0 2018 8 6 2	1900 6 3 1
2		1901 5 5 1
3		2017 14 2 0
4		2018 8 6 2
5
6
7
8	1900 6 3 1 1901 5 5 1 2017 14 2 0 2018 8 6 2	1900 6 3 1	1901 5 5 1	2017 14 2 0	2018 8 6 2
Sheet638

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE(SUBSTITUTE(" "&A$1," ",REPT(" ",LEN(A$1)),ROWS(B$1:B1)*4+1)," ",REPT(" ",LEN(A$1)),ROWS(B$1:B1)*4-3),LEN(A$1)+ROWS(B$1:B1),LEN(A$1)))
B8	=TRIM(MID(SUBSTITUTE(SUBSTITUTE(" "&$A1," ",REPT(" ",LEN($A1)),COLUMNS($B8:B8)*4+1)," ",REPT(" ",LEN($A1)),COLUMNS($B8:B8)*4-3),LEN($A1)+COLUMNS($B8:B8),LEN($A1)))

 

[jtakw]

After re-reading your post and looking thru other suggested solutions perhaps you mean you want your results as my sample below.

Formula copied down and across:

Book1
	A	B	C	D	E	F
12	1900 6 3 1 1901 5 5 1 2017 14 2 0 2018 8 6 2	1900	6	3	1
13		1901	5	5	1
14		2017	14	2	0
15		2018	8	6	2
16
Sheet638

Cell Formulas
Range	Formula
B12	=IF(COLUMNS($B12:B12)>4,"",TRIM(MID(SUBSTITUTE($A$12," ",REPT(" ",100)),(ROWS(B$12:B12)*4-3)*100-99+(COLUMNS($B12:B12)-1)*100,100)))