Text to Columns question

[vzachin]

hi,

in column A5 and down, i want to convert a Text to Columns. The columns will vary but the length will always be fixed. i run into a problem if one for the cell contains leading "0" zeros. i need to retain the zeroes.
here's my code

Sub test2()
myrange = Range("A5:A" & Range("A" & Rows.Count).End(xlUp).Row).Select
Selection.TextToColumns Destination:=Range("A5"), DataType:=xlFixedWidth, _
TrailingMinusNumbers:=True
End Sub

if i use the macro recorder
Sub test1()
'this will format correctly
myrange = Range("A5:A" & Range("A" & Rows.Count).End(xlUp).Row).Select
Selection.TextToColumns Destination:=Range("A5"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(16, 2), Array(32, 2)), TrailingMinusNumbers _
:=True
End Sub
but this will work and retain the leading zeros only if i have 3 columns.
how can i incorporate
FieldInfo:=Array(Array(0, 2), into sub test2?
i figure the 2 is for text.

or how can i re-write sub test1 so that it will work for any number of columns?


thanks
zach

[Bob Phillips]

What is the rationale for rows 12 and 13 not splitting into 6, i.e. with a space delimiter?

[vzachin]

hi xld,

the data is being scraped from a 3270 emulator where the data is in columns and rows.
for each file i scrape, the column width within the file is set but the data is not always the same as the column width. for example, if the column width is 8 characters, the data can be 0-8 characters.
the column width will vary for each file i scrape. sometimes the column width will be 12 characters, maybe 2.
so, i thought it would be easier if i scrape each row and then evoke the Text to Columns with a fixed width. until i ran into this issue with the zeros.
hope this makes sense.
thanks for looking at this
zach