hello-
i have written a macro to parse data in to four columns using the text to columns fixed width option, but unfortunately the data i get changes its spacing and configuration every day, meaning that the fixedwidth columns dont separate the data correctly. below is how it looks some of the time.
46632hac5 Jpmcc 2007-ld12 a5 19,340,005 315
0738qac5 bscms 2007-pw17 a5 23,142,005 265
61746wcz5 msdwc 2000-prin a5 3,600,005 305
32108hp75 bacm 2007-2 a5 2,000,005 465
46630edf5 lbubs 2006-c1 a5 1,000,005 285
i would like to automatically run a macro that puts this data in to 4 columns and recognizes when to put a separator after a certain value or number of characters. i have tried the space and tab delimited options and those did not work either.
to give you an idea of where i need columns to go, after every 5, i would like a separator. unfortunately, i just put the 5 there to make it easier to understand, those values will change all of the time and may also be letters in the case of the first column.
any ideas would be greatly appreciated.
here is what the macro looks like as of now.
Sub TextDataToColumns()
FinalRow = Cells(Rows.Count, 11).End(xlUp).Row
Range("K2:K" & FinalRow).TextToColumns Destination:=Range("K2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(35, 1), Array(45, 1)) _
, TrailingMinusNumbers:=True
End Sub
<table style="border-collapse: collapse; width: 44px; height: 168px;" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt; width: 57pt;" width="76" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td> </tr> </tbody></table>
i have written a macro to parse data in to four columns using the text to columns fixed width option, but unfortunately the data i get changes its spacing and configuration every day, meaning that the fixedwidth columns dont separate the data correctly. below is how it looks some of the time.
46632hac5 Jpmcc 2007-ld12 a5 19,340,005 315
0738qac5 bscms 2007-pw17 a5 23,142,005 265
61746wcz5 msdwc 2000-prin a5 3,600,005 305
32108hp75 bacm 2007-2 a5 2,000,005 465
46630edf5 lbubs 2006-c1 a5 1,000,005 285
i would like to automatically run a macro that puts this data in to 4 columns and recognizes when to put a separator after a certain value or number of characters. i have tried the space and tab delimited options and those did not work either.
to give you an idea of where i need columns to go, after every 5, i would like a separator. unfortunately, i just put the 5 there to make it easier to understand, those values will change all of the time and may also be letters in the case of the first column.
any ideas would be greatly appreciated.
here is what the macro looks like as of now.
Sub TextDataToColumns()
FinalRow = Cells(Rows.Count, 11).End(xlUp).Row
Range("K2:K" & FinalRow).TextToColumns Destination:=Range("K2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(35, 1), Array(45, 1)) _
, TrailingMinusNumbers:=True
End Sub
<table style="border-collapse: collapse; width: 44px; height: 168px;" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt; width: 57pt;" width="76" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td></tr><tr style="height: 14.25pt;" height="19"><td class="xl66" style="height: 14.25pt;" height="19">
</td> </tr> </tbody></table>