Text to Columns Problem

steve case

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

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,287
Office Version
  1. 365
Platform
  1. Windows
Is the data all in one cell or across the row?
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

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
 

steve case

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

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,287
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,464
Office Version
  1. 2016
Platform
  1. Windows
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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">1900 6 3 1 1901 5 5 1 2017 14 2 0 2018 8 6 2</td><td style=";">1900 6 3 1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">1901 5 5 1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style=";">2017 14 2 0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style=";">2018 8 6 2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">1900 6 3 1 1901 5 5 1 2017 14 2 0 2018 8 6 2</td><td style=";">1900 6 3 1</td><td style=";">1901 5 5 1</td><td style=";">2017 14 2 0</td><td style=";">2018 8 6 2</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet638</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">" "&A$1," ",REPT(<font color="Teal">" ",LEN(<font color="#FF00FF">A$1</font>)</font>),ROWS(<font color="Teal">B$1:B1</font>)*4+1</font>)," ",REPT(<font color="Purple">" ",LEN(<font color="Teal">A$1</font>)</font>),ROWS(<font color="Purple">B$1:B1</font>)*4-3</font>),LEN(<font color="Green">A$1</font>)+ROWS(<font color="Green">B$1:B1</font>),LEN(<font color="Green">A$1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B8</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">" "&$A1," ",REPT(<font color="Teal">" ",LEN(<font color="#FF00FF">$A1</font>)</font>),COLUMNS(<font color="Teal">$B8:B8</font>)*4+1</font>)," ",REPT(<font color="Purple">" ",LEN(<font color="Teal">$A1</font>)</font>),COLUMNS(<font color="Purple">$B8:B8</font>)*4-3</font>),LEN(<font color="Green">$A1</font>)+COLUMNS(<font color="Green">$B8:B8</font>),LEN(<font color="Green">$A1</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,464
Office Version
  1. 2016
Platform
  1. Windows
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">1900 6 3 1 1901 5 5 1 2017 14 2 0 2018 8 6 2</td><td style="text-align: right;;">1900</td><td style="text-align: right;;">6</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;">1901</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;">2017</td><td style="text-align: right;;">14</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;">2018</td><td style="text-align: right;;">8</td><td style="text-align: right;;">6</td><td style="text-align: right;;">2</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet638</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B12</th><td style="text-align:left">=IF(<font color="Blue">COLUMNS(<font color="Red">$B12:B12</font>)>4,"",TRIM(<font color="Red">MID(<font color="Green">SUBSTITUTE(<font color="Purple">$A$12," ",REPT(<font color="Teal">" ",100</font>)</font>),(<font color="Purple">ROWS(<font color="Teal">B$12:B12</font>)*4-3</font>)*100-99+(<font color="Purple">COLUMNS(<font color="Teal">$B12:B12</font>)-1</font>)*100,100</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

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
Top