Text to Columns Fixed Width Using Predefined Lengths

**easycompany16** · 02-03-2012, 10:09 PM

Hi Everyone,

I was hoping you could help me solve a problem. I'm trying to make a macro that will turn a fixed length text file into columns. So I Googled around and found this piece of code that works:

Sub MySplit()
Columns(1).TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(12, 1), Array(19, 1), _
Array(24, 1))
End Sub

I want to take it a step further. Often times, I know the position of where to split beforehand. So rather than having to manually type that it in every time, I want excel to pass an array of position values into the text to columns splitter. Is this possible? I imagine it would look something like this.

Sub MySplit()
myArray = range("R65:R125") 'My list of position values
Columns(1).TextToColumns Destination:=range("A1"), DataType:=xlFixedWidth, FieldInfo:=myArray()
End Sub

I've been trying for the last two hours, and it's been driving me crazy

Any help would be greatly appreciated.

**JBeaucaire** · 02-03-2012, 10:24 PM

No workbook to test this on, so, does this help:

myArray = Range("R65:S125")

or

myArray = Application.Transpose(Range("R65:S125"))

**easycompany16** · 02-04-2012, 12:10 AM

Hmm, no it doesn't seem to work, Keeps error-ing out.

**JBeaucaire** · 02-04-2012, 04:41 AM

I've called in some other minds to look at this. Here's a test workbook I created to try out on idea posited by another contributor, but it isn't working yet either...

**kvsrinivasamurthy** · 02-04-2012, 06:56 AM

Pl try this macro on Sheet2.
It is for max 15 values in R & S columns ie Fot 15 arrays.You can use for less than 15 nos without any changes.
If you want to use for > 15 make appropriate changes in macro.Feel free for any clarifications.

code:

Sub txtcolfw()

Dim txtrng As Range
Dim Lro, T As Integer
Dim Ary  As String
Dim R(15), S(15) As Integer

Lro = Sheets("Sheet2").Range("A1").End(xlDown).Row

For T = 1 To 15
If Cells(T, "R") = "" Then R(T) = 0 Else R(T) = Cells(T, "R")
If Cells(T, "S") = "" Then S(T) = 1 Else S(T) = Cells(T, "S")
Next T

Set txtrng = Sheets("Sheet2").Range("A1:A" & Lro)
    
    txtrng.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(R(1), S(1)), Array(R(2), S(2)), Array(R(3), S(3)), _
        Array(R(4), S(4)), Array(R(5), S(5)), Array(R(6), S(6)), Array(R(7), S(7)), _
        Array(R(8), S(8)), Array(R(9), S(9)), Array(R(10), S(10)), Array(R(11), S(11)), _
        Array(R(12), S(12)), Array(R(13), S(13)), Array(R(14), S(14)), Array(R(15), S(15))), _
        TrailingMinusNumbers:=True
End Sub

**easycompany16** · 02-14-2012, 03:57 PM

Thanks kvsrinivasamurthy! Works exactly how I wanted it to.

I was going through the code trying to understand it. Are these assumption correct?
R(T)=Position to split
S(T)= Column of fixed width data (Defaults to column 1 or A)
Ary=Nothing
Lro=Lowest row of fixed width data
txtrange=range of the fixed width data. Looks like you append Lro for the end of the range

**easycompany16** · 02-15-2012, 02:45 PM

So I modified you're code a little bit, but I'm having an issue with pasting the results in a different tab. It keeps saying the destination is invalid. Any ideas?

Sub txtcolfw()

Dim txtrng As Range
Dim Lro, T As Integer
Dim Ary  As String
Dim R(205), S(205) As Integer

Lro = Sheets("InputText").Range("A1").End(xlDown).Row 'Get the last row of the fixed width data

For T = 1 To 205
If Sheets("Config").Cells(T, "R") = "" Then R(T) = 0 Else R(T) = Sheets("Config").Cells(T, "R") 'If cell is blank, it will set the position to split at to 0, so that nothing will happen.
If Sheets("Config").Cells(T, "S") = "" Then S(T) = 1 Else S(T) = Sheets("Config").Cells(T, "S") 'Dictates Column. Default is Column 1. Leave as is
Next T

Set txtrng = Sheets("InputText").Range("A1:A" & Lro) 'sets the range of the fixed width data by appending Lro

txtrng.TextToColumns Destination:=Sheets("OutputText").Range("A2"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(R(1), S(1)), Array(R(2), S(2)), Array(R(3), S(3)), Array(R(4), S(4)), Array(R(5), S(5)), Array(R(6), S(6)), Array(R(7), S(7)), Array(R(8), S(8)), Array(R(9), S(9)), Array(R(10), S(10)), Array(R(11), S(11)), Array(R(12), S(12)), Array(R(13), S(13)), Array(R(14), S(14)), Array(R(15), S(15)), _
Array(R(16), S(16)), Array(R(17), S(17)), Array(R(18), S(18)), Array(R(19), S(19)), Array(R(20), S(20)), Array(R(21), S(21)), Array(R(22), S(22)), Array(R(23), S(23)), Array(R(24), S(24)), Array(R(25), S(25)), _
Array(R(26), S(26)), Array(R(27), S(27)), Array(R(28), S(28)), Array(R(29), S(29)), Array(R(30), S(30)), Array(R(31), S(31)), Array(R(32), S(32)), Array(R(33), S(33)), Array(R(34), S(34)), Array(R(35), S(35)), _
Array(R(36), S(36)), Array(R(37), S(37)), Array(R(38), S(38)), Array(R(39), S(39)), Array(R(40), S(40)), Array(R(41), S(41)), Array(R(42), S(42)), Array(R(43), S(43)), Array(R(44), S(44)), Array(R(45), S(45)), _
Array(R(46), S(46)), Array(R(47), S(47)), Array(R(48), S(48)), Array(R(49), S(49)), Array(R(50), S(50)), Array(R(51), S(51)), Array(R(52), S(52)), Array(R(53), S(53)), Array(R(54), S(54)), Array(R(55), S(55)), Array(R(56), S(56)), Array(R(57), S(57)), Array(R(58), S(58)), Array(R(59), S(59)), Array(R(60), S(60)), Array(R(61), S(61)), Array(R(62), S(62)), Array(R(63), S(63)), Array(R(64), S(64)), Array(R(65), S(65)), _
Array(R(66), S(66)), Array(R(67), S(67)), Array(R(68), S(68)), Array(R(69), S(69)), Array(R(70), S(70)), Array(R(71), S(71)), Array(R(72), S(72)), Array(R(73), S(73)), Array(R(74), S(74)), Array(R(75), S(75)), _
Array(R(76), S(76)), Array(R(77), S(77)), Array(R(78), S(78)), Array(R(79), S(79)), Array(R(80), S(80)), Array(R(81), S(81)), Array(R(82), S(82)), Array(R(83), S(83)), Array(R(84), S(84)), Array(R(85), S(85)), _
Array(R(86), S(86)), Array(R(87), S(87)), Array(R(88), S(88)), Array(R(89), S(89)), Array(R(90), S(90)), Array(R(91), S(91)), Array(R(92), S(92)), Array(R(93), S(93)), Array(R(94), S(94)), Array(R(95), S(95)), _
Array(R(96), S(96)), Array(R(97), S(97)), Array(R(98), S(98)), Array(R(99), S(99)), Array(R(100), S(100)), Array(R(101), S(101)), Array(R(102), S(102)), Array(R(103), S(103)), Array(R(104), S(104)), Array(R(105), S(105)), Array(R(106), S(106)), Array(R(107), S(107)), Array(R(108), S(108)), Array(R(109), S(109)), Array(R(110), S(110)), Array(R(111), S(111)), Array(R(112), S(112)), _
Array(R(113), S(113)), Array(R(114), S(114)), Array(R(115), S(115)), Array(R(116), S(116)), Array(R(117), S(117)), Array(R(118), S(118)), Array(R(119), S(119)), Array(R(120), S(120)), Array(R(121), S(121)), Array(R(122), S(122)), Array(R(123), S(123)), Array(R(124), S(124)), Array(R(125), S(125)), Array(R(126), S(126)), Array(R(127), S(127)), Array(R(128), S(128)), Array(R(129), S(129)), Array(R(130), S(130)), Array(R(131), S(131)), Array(R(132), S(132)), _
Array(R(133), S(133)), Array(R(134), S(134)), Array(R(135), S(135)), Array(R(136), S(136)), Array(R(137), S(137)), Array(R(138), S(138)), Array(R(139), S(139)), Array(R(140), S(140)), Array(R(141), S(141)), Array(R(142), S(142)), Array(R(143), S(143)), Array(R(144), S(144)), Array(R(145), S(145)), Array(R(146), S(146)), Array(R(147), S(147)), Array(R(148), S(148)), Array(R(149), S(149)), Array(R(150), S(150)), Array(R(151), S(151)), Array(R(152), S(152)), Array(R(153), S(153)), _
Array(R(154), S(154)), Array(R(155), S(155)), Array(R(156), S(156)), Array(R(157), S(157)), Array(R(158), S(158)), Array(R(159), S(159)), Array(R(160), S(160)), Array(R(161), S(161)), Array(R(162), S(162)), Array(R(163), S(163)), _
Array(R(164), S(164)), Array(R(165), S(165)), Array(R(166), S(166)), Array(R(167), S(167)), Array(R(168), S(168)), Array(R(169), S(169)), Array(R(170), S(170)), Array(R(171), S(171)), Array(R(172), S(172)), Array(R(173), S(173)), Array(R(174), S(174)), Array(R(175), S(175)), Array(R(176), S(176)), Array(R(177), S(177)), _
Array(R(178), S(178)), Array(R(179), S(179)), Array(R(180), S(180)), Array(R(181), S(181)), Array(R(182), S(182)), Array(R(183), S(183)), Array(R(184), S(184)), Array(R(185), S(185)), Array(R(186), S(186)), Array(R(187), S(187)), Array(R(188), S(188)), Array(R(189), S(189)), Array(R(190), S(190)), Array(R(191), S(191)), _
Array(R(192), S(192)), Array(R(193), S(193)), Array(R(194), S(194)), Array(R(195), S(195)), Array(R(196), S(196)), Array(R(197), S(197)), Array(R(198), S(198)), Array(R(199), S(199)), Array(R(200), S(200)), Array(R(201), S(201)), Array(R(202), S(202)), Array(R(203), S(203)), Array(R(204), S(204)), Array(R(205), S(205))), TrailingMinusNumbers:=True
    
End Sub

**snb** · 02-04-2012, 10:25 AM

You can use

Sub snb1()
 sn = Cells(1, 18).CurrentRegion
 ReDim sp(1 To UBound(sn))

 For j = 1 To UBound(sn)
  sp(j) = Application.Index(sn, j)
 Next

 Columns(1).TextToColumns , 2, FieldInfo:=sp
End Sub

or

Sub snb()
  sn = Cells(1, 18).CurrentRegion
  sp = Application.Transpose(Application.Index(sn, , 1))
  
  For j = 1 To UBound(sn)
   sp(j) = Application.Index(sn, j)
  Next

 Columns(1).TextToColumns , 2, FieldInfo:=sp
End Sub

or if you want to specify the limits in VBA and not in a worksheet:

Sub snb3()
  ReDim sn(0, 1)
  sn(0, 1) = 1
  sp = Array(0, 4, 12, 19, 24)
  
  For j = 0 To UBound(sp)
    sn(0, 0) = sp(j)
    sp(j) = sn
  Next

 Columns(1).TextToColumns , 2, FieldInfo:=sp
End Sub

NB. You 'll have to be aware that sp is a 0-dimensional array, that contains 0-dimensional arrays of 2 items each
if you want to return the values in array sp, you can do so using:

x3=sp(0)(0,0) : 0
x4=sp(0)(0,1) : 1
x5=sp(1)(0,0) : 4
x6=sp(1)(0,1) : 1

etc.