Text to Columns Problem

steve case

Well-known Member
Joined
Apr 10, 2002
Messages
820
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
If you data is in row 1 then perhaps this for results starting "A2".
Code:
[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
 
Upvote 0
just out of curiosity...

like this?

rawResult.1Result.2Result.3Result.4
1900 6 3 1 1901 5 5 1 2017 14 2 0 2018 8 6 21900631
1901551
20171420
2018862
 
Upvote 0
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".
Code:
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
 
Last edited:
Upvote 0
You can try this if code is ok. Data in cell A1 of activesheet. Results in A3:D3 downwards:

Code:
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
 
Last edited:
Upvote 0
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
ABCDE
11900 6 3 1 1901 5 5 1 2017 14 2 0 2018 8 6 21900 6 3 1
21901 5 5 1
32017 14 2 0
42018 8 6 2
5
6
7
81900 6 3 1 1901 5 5 1 2017 14 2 0 2018 8 6 21900 6 3 11901 5 5 12017 14 2 02018 8 6 2
Sheet638
Cell Formulas
RangeFormula
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)))
 
Last edited:
Upvote 0
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
ABCDEF
121900 6 3 1 1901 5 5 1 2017 14 2 0 2018 8 6 21900631
131901551
1420171420
152018862
16
Sheet638
Cell Formulas
RangeFormula
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)))
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top