Text To Columns Macro (to avoid out of memory error)

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Try this - launch the macro then go make a cup of coffee or whatevver


Code:
Sub TTC()
Dim LR As Long, i As Long, j As Integer, X As Variant
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
LR = Range("D" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("D" & i)
        X = Split(.Value, "~")
        For j = LBound(X) To UBound(X)
            .Offset(, j).Value = X(j)
        Next j
    End With
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
E
 
L

Legacy 14611

Guest
Dougf,

Several comments:

1. Before posting I generated 1,000,000 rows of test data for text to cols with about 20 ~'s to separate data over next 20 cols to right of colD. With 2gb memory it ran fine. Timed that at 8.6 seconds on the excel timer.

2. That code ran in computer memory rather than via the worksheet. Faster that way, but uses more memory.

3. You can modify it, as in the following, to run via your worksheet. This should save on memory use, but will take longer to run. If it gives an error message, you'll also be able to read off from your worksheet how many rows it does before giving error. This should help to track down the problem.
Code:
Sub txt_to_colcells()
Dim n As Long, i As Long, j As Long, y
n = Cells(Rows.Count, "d").End(xlUp).Row
For i = 1 To n
    y = Split(Cells(i, "d"), "~", -1)
For j = 0 To UBound(y)
    Cells(i, j + 5) = y(j)
Next j, i
End Sub
 

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,347
Hi dougf,
I"m a bit stuck because it looks like XLS2007 don't accept the statement RIGHT when it's OK for XLS_XP, I will investigate.
NB:
In VoG code the last statement E certainly needs to be changed by END SUB
 

dougf

New Member
Joined
Mar 15, 2009
Messages
28
Excel still manages to lock up (there are many more than 20 columns, all the way up to ABE).

VoG,

Your macro worked well. Is there a quick change we can make to force excel to treat the new cells as text? It's strange but Excel decided to round values and add in a zero in front of all of the cells because every entry should either start with a "." or a "D." and thus appears like a decimal which it is not.

Rugila,
Your macro also works well. But, here's the killer, I need to get rid of the original column D, and now this causes an out of memory error as soon as I click the button... I have a feeling some Microsoft programmer left some functions that require ints instead of longs, or an old maximum array size.

I assume solving this is just a matter of adding an extra line into the formula to shift the previous row one left?

Thanks again!
 
Last edited:

dougf

New Member
Joined
Mar 15, 2009
Messages
28
Another problem I noticed is that when Excel tries to save the file (Save As...) after the file has its new columns, a new window appears saying "Trying to connect to folder" and displays "Contacting C:\.[...]..\Documents"

Any thoughts on a way to also force excel to save the document without panicking like this?
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Greetings,

Not sure about #15, but was curious as to whether you have tried breaking the text-to-columns up in chunks?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Text2Col_GetChunks()<br>    <br>Dim _<br>lLRow           <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br>lRemainder      <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br>lChunkCount     <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br>i               <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br>rngCurrent      <SPAN style="color:#00007F">As</SPAN> Range<br>    <br><SPAN style="color:#007F00">'Dim Start As Single: Start = Timer</SPAN><br>    <br><SPAN style="color:#00007F">Const</SPAN> CHUNK_SIZE <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 10000    <SPAN style="color:#007F00">'<---Increase to suit.</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet <SPAN style="color:#007F00">'Sheet2</SPAN><br>        <br>        lLRow = .Cells(Rows.Count, "D").End(xlUp).Row<br>        <br>        lChunkCount = lLRow \ CHUNK_SIZE<br>        lRemainder = lLRow Mod CHUNK_SIZE<br>        <br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> lChunkCount<br>            <SPAN style="color:#00007F">Set</SPAN> rngCurrent = .Range(.Cells((i * CHUNK_SIZE + 1) - CHUNK_SIZE, "D"), _<br>                                    .Cells(i * CHUNK_SIZE, "D"))<br>            <br>            rngCurrent.TextToColumns Destination:=rngCurrent(1, 1), _<br>                                     DataType:=xlDelimited, _<br>                                     TextQualifier:=xlTextQualifierNone, _<br>                                     Other:=True, OtherChar:="~"<br>        <SPAN style="color:#00007F">Next</SPAN><br>        <br>        <SPAN style="color:#00007F">If</SPAN> lRemainder > 0 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> rngCurrent = .Range(.Cells((i * CHUNK_SIZE + 1) - CHUNK_SIZE, "D"), _<br>                                    .Cells(.Cells( _<br>                                            (i * CHUNK_SIZE + 1) - CHUNK_SIZE, "D").Row _<br>                                            + lRemainder - 1, "D" _<br>                                           ) _<br>                                    )<br>            <br>            rngCurrent.TextToColumns Destination:=rngCurrent(1, 1), _<br>                                     DataType:=xlDelimited, _<br>                                     TextQualifier:=xlTextQualifierNone, _<br>                                     Other:=True, OtherChar:="~"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <br><SPAN style="color:#007F00">'Debug.Print "Text2Col_GetChunks: " & Timer - Start</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

NOTE: Could you specify which of Rugila's you are referring to?

Hope this helps,

Mark
 
Last edited:

dougf

New Member
Joined
Mar 15, 2009
Messages
28
Mark,

Thanks. I am responding to Rugila's second (optimized) formula.

Will your formula properly keep the decimal-looking cell contents as the text they should remain?

Best,
Doug
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Hi Doug,

I don't have the data you have in front of you. I do believe the code is worth testing, as at least with the fake data I cobbled together, the speed increase was noticeable.

In the data you are parsing, can we count on a certain amoount of columns, or do the number of columns change from row to row?

Mark
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
ACK! Dang ten minute limit and a slow typist do not mix.

Anyways, I tossed in:

.00001~orange~pecos~D2.2~Lime~.10~orange~pecos~2.2.2~Lime~00.001

... and it seems to work fine.

Mark
 

dougf

New Member
Joined
Mar 15, 2009
Messages
28
ACK! Dang ten minute limit and a slow typist do not mix.

Anyways, I tossed in:

.00001~orange~pecos~D2.2~Lime~.10~orange~pecos~2.2.2~Lime~00.001

... and it seems to work fine.

Mark
GTO,

The problem is many of these identifiers begin with a period and are 7 digits long. Excel is still adding in the 0 prefix, and truncating off zeros at the end, which invalidates the identifier.
 

Forum statistics

Threads
1,084,880
Messages
5,380,425
Members
401,677
Latest member
BobH

Some videos you may like

This Week's Hot Topics

Top