Can anyone tell me how to split a row of text data into two rows so I can manipulate them independently? I want to end the first part of the line at character 3752. The total length of the record is 3856. Thanks
Printable View
Can anyone tell me how to split a row of text data into two rows so I can manipulate them independently? I want to end the first part of the line at character 3752. The total length of the record is 3856. Thanks
Hi Austen,
This doesn't use the Split function, which splits up a line of text based on a delimiter, but should be what you're looking for:[vba] Dim tempStr As String, str1 As String, str2 As String
tempStr = Range("A1").Text 'cell containing long string
str1 = Left(tempStr, 3752)
str2 = Mid(tempStr, 3753)[/vba]Matt
Hi Matt,
Could I then perform arrays on the rows? Then go to the next record?
Perhaps I should have asked what you're trying to accomplish?
The following splits a single cell into two parts of an array[vba]Dim tempStr As String, yourArray() As String
tempStr = Range("A1").Text 'cell containing long string
ReDim yourArray(1)
yourArray(0) = Left(tempStr, 3752)
yourArray(1) = Mid(tempStr, 3753)[/vba]
Or if you wanted to have a 2-dimensional array with all cells in a column (for example column A):[vba] Dim yourArray() As String, RG As Range, CellData(), R As Long
Set RG = Intersect(Columns("A"), ActiveSheet.UsedRange)
If RG Is Nothing Then Exit Sub
CellData = RG.Value 'create array of values from usedrange in A
ReDim yourArray(1, UBound(CellData, 1) - 1) 'redim array to be used for split text
For R = 1 To UBound(CellData, 1) 'loop through first column in value array
yourArray(0, R - 1) = Left(CellData(R, 1), 3752)
yourArray(1, R - 1) = Mid(CellData(R, 1), 3753)
Next
'now for each usedcell in column A, yourarray contains (at index X):
' yourarray(0, X) - first 3752 characters of cell
' yourarray(1, X) - remainder of cell starting in character 3753[/vba]
Sorry, more explaination. What I want to do is read the long string from a text file, split it (as above), use an array to parse each section into cells with each section being on a new row, then read the next record (loop). What I have is more cells than Excel has columns. Hence I need to parse as in field 1 = A1, field 2 = B1, etc. and end up stacking part of the record. Its not pretty but that is the only solution I could come up with.
I just want to make sure I understand..
Do you have a text file with very long lines in it, and you want each line split at that point, with the first part going into column A, and the rest into column B?
You mention splitting it into rows and into A1/B1.. so I'm not sure which you're looking for.
Or do you want to split the entire file into blocks of 3856 characters? and for each block put the first 3752 in A and the next 104 into column B?
austenr
Could you please provide some sample data?
You've posted this question in various forums and you've had plenty of responses.
But you still haven't given an example of your data or how you want to parse it.
It's kinda hard to help without that sort of information.:)
OK. I have attached a sample file of what I want the output to be. If you think of the sample as being one continuous line with no delimiters there are 300 fields (i can deal with the length of each field if you can get me to that point) and the number of records will vary but will be in the neighborhod of 2000 to 10000. Hope this clarifies it. One more thing. I had a post that Norie will rmemeber from a few days ago about arrays. That is the logic I really want to follow but if you have a better way I am open to that too.
austenr
I really don't see how we can help you.
We need more information.
OK. I have attached one record with all sensitive data stripped out. This record has more fields than 256 the EXCEL limit. I have gotten my array to work up to column IV the last column available. I would like to split this record at character 3752 in order to be able to place the remaining fields that do not fit on the first row onto the second row as a continuation of column IV (which if you had more columns available would be column IW). I do not know how to make it any plainer.
Austen,
I can and will give you code that is doing what you're asking. What we're trying to do though is help you beyond that. If you could, for example, tell us where in that line of 3856 characters the 300 fields come from (where the fixed width columns are), it would make things a lot easier. But you're telling us you want the 3856 characters split over two lines (3752 in one, 104 in the other), so all we can do is give them to you in column A, and you can split them as you want.
Based on your 'combine two functions' question, I'm going to write this so it pastes the information on a new sheet within the activeworkbook. If you don't want that, let us know what you do want instead. I could not find the question you referred to regarding arrays. This routine will import the text file into an array first, then puts the contents into excel (avoiding a new workbook, unless a first workbook does not already exist).[vba]Sub AustenSplitFile()
Dim vFile As String, vFF As Long, tempStr As String
Dim FileCont() As String, Cnt As Long, i As Long
vFile = Application.GetOpenFilename("Text Files,*.txt,All Files,*.*")
If LCase(vFile) = "false" Then Exit Sub
vFF = FreeFile
Open vFile For Input As #vFF
Do Until EOF(vFF)
Line Input #vFF, tempStr
ReDim Preserve FileCont(Cnt + 1)
FileCont(Cnt) = Left$(tempStr, 3752)
FileCont(Cnt + 1) = Mid$(tempStr, 3753)
Cnt = Cnt + 2
Loop
Close #vFF
Application.ScreenUpdating = False
If ActiveWorkbook Is Nothing Then
Workbooks.Add -4167
Else
Sheets.Add After:=Sheets(Sheets.Count)
End If
For i = 0 To Cnt - 2 Step 2
Cells(i + 1, 1) = FileCont(i)
Cells(i + 2, 1) = FileCont(i + 1)
Next
Application.ScreenUpdating = False
End Sub[/vba]Matt
Hi Matt. Thanks for the code. What I have working successfully is this VBscript:
[VBA]
Sub FormatCpExtractRecord()
Dim myarray As Variant
Dim strTextFile As Variant
If TypeName(strTextFile) = "Boolean" Then Exit Sub
strTextFile = Application.GetOpenFilename("Text Files (*.txt, *.txt")
Workbooks.OpenText strTextFile, , , xlFixedWidth, , , , , , , , , Array(Array(0, 1), _
Array(1, 1), Array(3, 1), Array(15, 1), Array(50, 1), Array(75, 1), _
Array(100, 1), Array(110, 1), Array(111, 1), Array(115, 1), Array(116, 1), Array(117, 1), _
Array(128, 1), Array(129, 1), Array(140, 1), Array(141, 1), Array(152, 1), Array(153, 1), _
Array(164, 1), Array(165, 1), Array(177, 1), Array(189, 1), Array(201, 1), Array(213, 1), _
Array(217, 1), Array(221, 1), Array(225, 1), Array(229, 1), Array(233, 1), Array(237, 1), _
Array(241, 1), Array(245, 1), Array(256, 1), Array(266, 1), Array(276, 1), Array(287, 1), _
Array(289, 1), Array(301, 1), Array(313, 1), Array(338, 1), Array(340, 1), Array(352, 1), _
Array(361, 1), Array(376, 1), Array(381, 1), Array(386, 1), Array(387, 1), Array(388, 1), _
Array(413, 1), Array(423, 1), Array(435, 1), Array(445, 1), Array(471, 1), Array(475, 1), _
Array(486, 1), Array(487, 1), Array(490, 1), Array(491, 1), Array(492, 1), Array(494, 1), _
Array(496, 1), Array(498, 1), Array(500, 1), Array(502, 1), Array(504, 1), Array(506, 1), _
Array(508, 1), Array(510, 1), Array(512, 1), Array(514, 1), Array(527, 1), Array(540, 1), _
Array(553, 1), Array(566, 1), Array(579, 1), Array(582, 1), Array(586, 1), Array(588, 1), _
Array(590, 1), Array(592, 1), Array(594, 1), Array(596, 1), Array(598, 1), Array(600, 1), _
Array(602, 1), Array(604, 1), Array(607, 1), Array(658, 1), Array(669, 1), Array(680, 1), _
Array(698, 1), Array(745, 1), Array(805, 1), Array(861, 1), Array(917, 1), Array(948, 1), Array(951, 1), Array(967, 1), Array(984, 1), Array(1020, 1), Array(1046, 1), Array(1072, 1), Array(1123, 1), Array(1134, 1), Array(1145, 1), Array(1165, 1), Array(1214, 1), Array(1270, 1), Array(1326, 1), Array(1382, 1), Array(1413, 1), Array(1416, 1), Array(1432, 1), Array(1449, 1), Array(1475, 1), Array(1478, 1), Array(1514, 1), Array(1541, 1), Array(1566, 1), Array(1622, 1), Array(1678, 1), Array(1734, 1), Array(1765, 1), Array(1768, 1), Array(1784, 1), Array(1788, 1), Array(1792, 1), Array(1873, 1), Array(1888, 1), Array(1903, 1), Array(1914, 1), Array(1996, 1), Array(1999, 1), Array(2050, 1), Array(2076, 1), Array(2102, 1), Array(2158, 1), Array(2214, 1), Array(2270, 1), Array(2301, 1), Array(2304, 1), Array(2320, 1), Array(2324, 1), Array(2328, 1), Array(2409, 1), Array(2424, 1), Array(2435, 1), Array(2447, 1), Array(2451, 1), Array(2463, 1), _
Array(2463, 1), Array(2467, 1), Array(2479, 1), Array(2483, 1), Array(2495, 1), Array(2499, 1), _
Array(2508, 1), Array(2510, 1), Array(2515, 1), Array(2524, 1), Array(2536, 1), Array(2546, 1), Array(2550, 1), Array(2552, 1), Array(2554, 1), Array(2557, 1), Array(2560, 1), Array(2562, 1), Array(2576, 1), Array(2627, 1), Array(2683, 1), Array(2739, 1), Array(2795, 1), Array(2826, 1), Array(2829, 1), Array(2845, 1), Array(2850, 1), Array(2856, 1), Array(2872, 1), Array(2888, 1), Array(2894, 1), Array(2896, 1), Array(2898, 1), Array(2900, 1), Array(2912, 1), Array(2914, 1), Array(2926, 1), Array(2938, 1), Array(2950, 1), Array(2952, 1), Array(2964, 1), Array(2965, 1), Array(2966, 1), Array(2967, 1), Array(2969, 1), Array(2971, 1), Array(2973, 1), Array(2975, 1), Array(2977, 1), Array(2978, 1), Array(2979, 1), Array(2981, 1), Array(2982, 1), Array(2984, 1), Array(2985, 1), Array(2988, 1), Array(2990, 1), Array(2992, 1), Array(2994, 1), Array(2996, 1), Array(2998, 1), Array(3011, 1), Array(3024, 1), Array(3026, 1), Array(3029, 1), Array(3041, 1), _
Array(3045, 1), Array(3075, 1), Array(3101, 1), Array(3127, 1), Array(3178, 1), Array(3198, 1), Array(3209, 1), Array(3221, 1), Array(3222, 1), Array(3232, 1), Array(3233, 1), Array(3234, 1), Array(3252, 1), Array(3264, 1), Array(3276, 1), Array(3279, 1), Array(3281, 1), Array(3299, 1), Array(3311, 1), Array(3322, 1), Array(3333, 1), _
Array(3389, 1), Array(3445, 1), Array(3519, 1), Array(3532, 1), Array(3535, 1), Array(3551, 1), Array(3555, 1), Array(3559, 1), Array(3563, 1), Array(3576, 1), Array(3594, 1), Array(3606, 1), Array(3610, 1), Array(3612, 1), Array(3614, 1), Array(3626, 1), Array(3628, 1), Array(3630, 1), Array(3646, 1), _
Array(3660, 1), Array(3673, 1), Array(3675, 1), Array(3691, 1), Array(3703, 1), _
Array(3715, 1))
End Sub
[/VBA]
As you can see, this opens the text file and parses the fields into cells. However, I still have more data to parse from this one record up to position 3856. The problem is that the last cell populated with the above array is in column IV. Hence, my difficulty.
What I want to happen is find a way to continue parsing the data on the next row, which if you are just parsing one record would be row2, cell B1 and complete the record. My only difficulty is to get the parsing to continue on row 2 to complete the record.
In my example above, (Array(0, 1) is in cell A1, Array(1, 1) is in cell B1, all the way across the row until the last array element of Array(3715, 1)) which lands in cell IV1.
Element Array(3718, 1) should continue populating cells in column B1 until the rest of the record is parsed, then get the next record and start parsing it in C1. Hope this helps you.
austenr
I've downloaded your attachment, and I imagine Matt has too.
To me at least the data means nothing and I can't see any structure to determine how to parse it.
Can you explain how it should be parsed?
Hi Austen,
That works successfully?? You have 262 fields in there!
But assuming that it does work for you somehow, I can give you code to do as you need, though it won't be exactly as you wish. Based on this:
That is not possible, since the .OpenText function only does the 256 columns, and you cannot program the built-in function to span to the next line. You do have to split it up in the text file, then transfer it to the sheet.Quote:
Element Array(3718, 1) should continue populating cells in column B1 until the rest of the record is parsed, then get the next record and start parsing it in C1
Here is code showing your 262 fields, though it will only fill in the first 256. I can write a SplitLine2 function (or you can, following my lead here) for line 2 if you show us how that is broken down. In any case, give this a go, see how it works for you. The starting point/lengths are taken from the code you recently posted:[vba]Sub AustenSplitFile()
Dim vFile As String, vFF As Long, tempStr As String
Dim FileCont() As String, Cnt As Long, i As Long
vFile = Application.GetOpenFilename("Text Files,*.txt,All Files,*.*")
If LCase(vFile) = "false" Then Exit Sub
vFF = FreeFile
Open vFile For Input As #vFF
Do Until EOF(vFF)
Line Input #vFF, tempStr
ReDim Preserve FileCont(Cnt + 1)
FileCont(Cnt) = Left$(tempStr, 3752)
FileCont(Cnt + 1) = Mid$(tempStr, 3753)
Cnt = Cnt + 2
Loop
Close #vFF
Application.ScreenUpdating = False
If ActiveWorkbook Is Nothing Then
Workbooks.Add -4167
Else
Sheets.Add After:=Sheets(Sheets.Count)
End If
For i = 0 To Cnt - 2 Step 2
Cells(i + 1, 1).Resize(1, 256) = SplitLine1(FileCont(i))
Cells(i + 2, 1) = FileCont(i + 1)
Next
Application.ScreenUpdating = False
End Sub
Function SplitLine1(ByVal tempStr As String) As String()
Dim tempArr() As String
ReDim tempArr(1 To 1, 1 To 262)
tempArr(1, 1) = Mid$(tempStr, 1, 1)
tempArr(1, 2) = Mid$(tempStr, 2, 2)
tempArr(1, 3) = Mid$(tempStr, 4, 12)
tempArr(1, 4) = Mid$(tempStr, 16, 35)
tempArr(1, 5) = Mid$(tempStr, 51, 25)
tempArr(1, 6) = Mid$(tempStr, 76, 25)
tempArr(1, 7) = Mid$(tempStr, 101, 10)
tempArr(1, 8) = Mid$(tempStr, 111, 1)
tempArr(1, 9) = Mid$(tempStr, 112, 4)
tempArr(1, 10) = Mid$(tempStr, 116, 1)
tempArr(1, 11) = Mid$(tempStr, 117, 1)
tempArr(1, 12) = Mid$(tempStr, 118, 11)
tempArr(1, 13) = Mid$(tempStr, 129, 1)
tempArr(1, 14) = Mid$(tempStr, 130, 11)
tempArr(1, 15) = Mid$(tempStr, 141, 1)
tempArr(1, 16) = Mid$(tempStr, 142, 11)
tempArr(1, 17) = Mid$(tempStr, 153, 1)
tempArr(1, 18) = Mid$(tempStr, 154, 11)
tempArr(1, 19) = Mid$(tempStr, 165, 1)
tempArr(1, 20) = Mid$(tempStr, 166, 12)
tempArr(1, 21) = Mid$(tempStr, 178, 12)
tempArr(1, 22) = Mid$(tempStr, 190, 12)
tempArr(1, 23) = Mid$(tempStr, 202, 12)
tempArr(1, 24) = Mid$(tempStr, 214, 4)
tempArr(1, 25) = Mid$(tempStr, 218, 4)
tempArr(1, 26) = Mid$(tempStr, 222, 4)
tempArr(1, 27) = Mid$(tempStr, 226, 4)
tempArr(1, 28) = Mid$(tempStr, 230, 4)
tempArr(1, 29) = Mid$(tempStr, 234, 4)
tempArr(1, 30) = Mid$(tempStr, 238, 4)
tempArr(1, 31) = Mid$(tempStr, 242, 4)
tempArr(1, 32) = Mid$(tempStr, 246, 11)
tempArr(1, 33) = Mid$(tempStr, 257, 10)
tempArr(1, 34) = Mid$(tempStr, 267, 10)
tempArr(1, 35) = Mid$(tempStr, 277, 11)
tempArr(1, 36) = Mid$(tempStr, 288, 2)
tempArr(1, 37) = Mid$(tempStr, 290, 12)
tempArr(1, 38) = Mid$(tempStr, 302, 12)
tempArr(1, 39) = Mid$(tempStr, 314, 25)
tempArr(1, 40) = Mid$(tempStr, 339, 2)
tempArr(1, 41) = Mid$(tempStr, 341, 12)
tempArr(1, 42) = Mid$(tempStr, 353, 9)
tempArr(1, 43) = Mid$(tempStr, 362, 15)
tempArr(1, 44) = Mid$(tempStr, 377, 5)
tempArr(1, 45) = Mid$(tempStr, 382, 5)
tempArr(1, 46) = Mid$(tempStr, 387, 1)
tempArr(1, 47) = Mid$(tempStr, 388, 1)
tempArr(1, 48) = Mid$(tempStr, 389, 25)
tempArr(1, 49) = Mid$(tempStr, 414, 10)
tempArr(1, 50) = Mid$(tempStr, 424, 12)
tempArr(1, 51) = Mid$(tempStr, 436, 10)
tempArr(1, 52) = Mid$(tempStr, 446, 26)
tempArr(1, 53) = Mid$(tempStr, 472, 4)
tempArr(1, 54) = Mid$(tempStr, 476, 11)
tempArr(1, 55) = Mid$(tempStr, 487, 1)
tempArr(1, 56) = Mid$(tempStr, 488, 3)
tempArr(1, 57) = Mid$(tempStr, 491, 1)
tempArr(1, 58) = Mid$(tempStr, 492, 1)
tempArr(1, 59) = Mid$(tempStr, 493, 2)
tempArr(1, 60) = Mid$(tempStr, 495, 2)
tempArr(1, 61) = Mid$(tempStr, 497, 2)
tempArr(1, 62) = Mid$(tempStr, 499, 2)
tempArr(1, 63) = Mid$(tempStr, 501, 2)
tempArr(1, 64) = Mid$(tempStr, 503, 2)
tempArr(1, 65) = Mid$(tempStr, 505, 2)
tempArr(1, 66) = Mid$(tempStr, 507, 2)
tempArr(1, 67) = Mid$(tempStr, 509, 2)
tempArr(1, 68) = Mid$(tempStr, 511, 2)
tempArr(1, 69) = Mid$(tempStr, 513, 2)
tempArr(1, 70) = Mid$(tempStr, 515, 13)
tempArr(1, 71) = Mid$(tempStr, 528, 13)
tempArr(1, 72) = Mid$(tempStr, 541, 13)
tempArr(1, 73) = Mid$(tempStr, 554, 13)
tempArr(1, 74) = Mid$(tempStr, 567, 13)
tempArr(1, 75) = Mid$(tempStr, 580, 3)
tempArr(1, 76) = Mid$(tempStr, 583, 4)
tempArr(1, 77) = Mid$(tempStr, 587, 2)
tempArr(1, 78) = Mid$(tempStr, 589, 2)
tempArr(1, 79) = Mid$(tempStr, 591, 2)
tempArr(1, 80) = Mid$(tempStr, 593, 2)
tempArr(1, 81) = Mid$(tempStr, 595, 2)
tempArr(1, 82) = Mid$(tempStr, 597, 2)
tempArr(1, 83) = Mid$(tempStr, 599, 2)
tempArr(1, 84) = Mid$(tempStr, 601, 2)
tempArr(1, 85) = Mid$(tempStr, 603, 2)
tempArr(1, 86) = Mid$(tempStr, 605, 3)
tempArr(1, 87) = Mid$(tempStr, 608, 51)
tempArr(1, 88) = Mid$(tempStr, 659, 11)
tempArr(1, 89) = Mid$(tempStr, 670, 11)
tempArr(1, 90) = Mid$(tempStr, 681, 18)
tempArr(1, 91) = Mid$(tempStr, 699, 47)
tempArr(1, 92) = Mid$(tempStr, 746, 60)
tempArr(1, 93) = Mid$(tempStr, 806, 56)
tempArr(1, 94) = Mid$(tempStr, 862, 56)
tempArr(1, 95) = Mid$(tempStr, 918, 31)
tempArr(1, 96) = Mid$(tempStr, 949, 3)
tempArr(1, 97) = Mid$(tempStr, 952, 16)
tempArr(1, 98) = Mid$(tempStr, 968, 17)
tempArr(1, 99) = Mid$(tempStr, 985, 36)
tempArr(1, 100) = Mid$(tempStr, 1021, 26)
tempArr(1, 101) = Mid$(tempStr, 1047, 26)
tempArr(1, 102) = Mid$(tempStr, 1073, 51)
tempArr(1, 103) = Mid$(tempStr, 1124, 11)
tempArr(1, 104) = Mid$(tempStr, 1135, 11)
tempArr(1, 105) = Mid$(tempStr, 1146, 20)
tempArr(1, 106) = Mid$(tempStr, 1166, 49)
tempArr(1, 107) = Mid$(tempStr, 1215, 56)
tempArr(1, 108) = Mid$(tempStr, 1271, 56)
tempArr(1, 109) = Mid$(tempStr, 1327, 56)
tempArr(1, 110) = Mid$(tempStr, 1383, 31)
tempArr(1, 111) = Mid$(tempStr, 1414, 3)
tempArr(1, 112) = Mid$(tempStr, 1417, 16)
tempArr(1, 113) = Mid$(tempStr, 1433, 17)
tempArr(1, 114) = Mid$(tempStr, 1450, 26)
tempArr(1, 115) = Mid$(tempStr, 1476, 3)
tempArr(1, 116) = Mid$(tempStr, 1479, 36)
tempArr(1, 117) = Mid$(tempStr, 1515, 27)
tempArr(1, 118) = Mid$(tempStr, 1542, 25)
tempArr(1, 119) = Mid$(tempStr, 1567, 56)
tempArr(1, 120) = Mid$(tempStr, 1623, 56)
tempArr(1, 121) = Mid$(tempStr, 1679, 56)
tempArr(1, 122) = Mid$(tempStr, 1735, 31)
tempArr(1, 123) = Mid$(tempStr, 1766, 3)
tempArr(1, 124) = Mid$(tempStr, 1769, 16)
tempArr(1, 125) = Mid$(tempStr, 1785, 4)
tempArr(1, 126) = Mid$(tempStr, 1789, 4)
tempArr(1, 127) = Mid$(tempStr, 1793, 81)
tempArr(1, 128) = Mid$(tempStr, 1874, 15)
tempArr(1, 129) = Mid$(tempStr, 1889, 15)
tempArr(1, 130) = Mid$(tempStr, 1904, 11)
tempArr(1, 131) = Mid$(tempStr, 1915, 82)
tempArr(1, 132) = Mid$(tempStr, 1997, 3)
tempArr(1, 133) = Mid$(tempStr, 2000, 51)
tempArr(1, 134) = Mid$(tempStr, 2051, 26)
tempArr(1, 135) = Mid$(tempStr, 2077, 26)
tempArr(1, 136) = Mid$(tempStr, 2103, 56)
tempArr(1, 137) = Mid$(tempStr, 2159, 56)
tempArr(1, 138) = Mid$(tempStr, 2215, 56)
tempArr(1, 139) = Mid$(tempStr, 2271, 31)
tempArr(1, 140) = Mid$(tempStr, 2302, 3)
tempArr(1, 141) = Mid$(tempStr, 2305, 16)
tempArr(1, 142) = Mid$(tempStr, 2321, 4)
tempArr(1, 143) = Mid$(tempStr, 2325, 4)
tempArr(1, 144) = Mid$(tempStr, 2329, 81)
tempArr(1, 145) = Mid$(tempStr, 2410, 15)
tempArr(1, 146) = Mid$(tempStr, 2425, 11)
tempArr(1, 147) = Mid$(tempStr, 2436, 12)
tempArr(1, 148) = Mid$(tempStr, 2448, 4)
tempArr(1, 149) = Mid$(tempStr, 2452, 12)
tempArr(1, 150) = Mid$(tempStr, 2464, 0)
tempArr(1, 151) = Mid$(tempStr, 2464, 4)
tempArr(1, 152) = Mid$(tempStr, 2468, 12)
tempArr(1, 153) = Mid$(tempStr, 2480, 4)
tempArr(1, 154) = Mid$(tempStr, 2484, 12)
tempArr(1, 155) = Mid$(tempStr, 2496, 4)
tempArr(1, 156) = Mid$(tempStr, 2500, 9)
tempArr(1, 157) = Mid$(tempStr, 2509, 2)
tempArr(1, 158) = Mid$(tempStr, 2511, 5)
tempArr(1, 159) = Mid$(tempStr, 2516, 9)
tempArr(1, 160) = Mid$(tempStr, 2525, 12)
tempArr(1, 161) = Mid$(tempStr, 2537, 10)
tempArr(1, 162) = Mid$(tempStr, 2547, 4)
tempArr(1, 163) = Mid$(tempStr, 2551, 2)
tempArr(1, 164) = Mid$(tempStr, 2553, 2)
tempArr(1, 165) = Mid$(tempStr, 2555, 3)
tempArr(1, 166) = Mid$(tempStr, 2558, 3)
tempArr(1, 167) = Mid$(tempStr, 2561, 2)
tempArr(1, 168) = Mid$(tempStr, 2563, 14)
tempArr(1, 169) = Mid$(tempStr, 2577, 51)
tempArr(1, 170) = Mid$(tempStr, 2628, 56)
tempArr(1, 171) = Mid$(tempStr, 2684, 56)
tempArr(1, 172) = Mid$(tempStr, 2740, 56)
tempArr(1, 173) = Mid$(tempStr, 2796, 31)
tempArr(1, 174) = Mid$(tempStr, 2827, 3)
tempArr(1, 175) = Mid$(tempStr, 2830, 16)
tempArr(1, 176) = Mid$(tempStr, 2846, 5)
tempArr(1, 177) = Mid$(tempStr, 2851, 6)
tempArr(1, 178) = Mid$(tempStr, 2857, 16)
tempArr(1, 179) = Mid$(tempStr, 2873, 16)
tempArr(1, 180) = Mid$(tempStr, 2889, 6)
tempArr(1, 181) = Mid$(tempStr, 2895, 2)
tempArr(1, 182) = Mid$(tempStr, 2897, 2)
tempArr(1, 183) = Mid$(tempStr, 2899, 2)
tempArr(1, 184) = Mid$(tempStr, 2901, 12)
tempArr(1, 185) = Mid$(tempStr, 2913, 2)
tempArr(1, 186) = Mid$(tempStr, 2915, 12)
tempArr(1, 187) = Mid$(tempStr, 2927, 12)
tempArr(1, 188) = Mid$(tempStr, 2939, 12)
tempArr(1, 189) = Mid$(tempStr, 2951, 2)
tempArr(1, 190) = Mid$(tempStr, 2953, 12)
tempArr(1, 191) = Mid$(tempStr, 2965, 1)
tempArr(1, 192) = Mid$(tempStr, 2966, 1)
tempArr(1, 193) = Mid$(tempStr, 2967, 1)
tempArr(1, 194) = Mid$(tempStr, 2968, 2)
tempArr(1, 195) = Mid$(tempStr, 2970, 2)
tempArr(1, 196) = Mid$(tempStr, 2972, 2)
tempArr(1, 197) = Mid$(tempStr, 2974, 2)
tempArr(1, 198) = Mid$(tempStr, 2976, 2)
tempArr(1, 199) = Mid$(tempStr, 2978, 1)
tempArr(1, 200) = Mid$(tempStr, 2979, 1)
tempArr(1, 201) = Mid$(tempStr, 2980, 2)
tempArr(1, 202) = Mid$(tempStr, 2982, 1)
tempArr(1, 203) = Mid$(tempStr, 2983, 2)
tempArr(1, 204) = Mid$(tempStr, 2985, 1)
tempArr(1, 205) = Mid$(tempStr, 2986, 3)
tempArr(1, 206) = Mid$(tempStr, 2989, 2)
tempArr(1, 207) = Mid$(tempStr, 2991, 2)
tempArr(1, 208) = Mid$(tempStr, 2993, 2)
tempArr(1, 209) = Mid$(tempStr, 2995, 2)
tempArr(1, 210) = Mid$(tempStr, 2997, 2)
tempArr(1, 211) = Mid$(tempStr, 2999, 13)
tempArr(1, 212) = Mid$(tempStr, 3012, 13)
tempArr(1, 213) = Mid$(tempStr, 3025, 2)
tempArr(1, 214) = Mid$(tempStr, 3027, 3)
tempArr(1, 215) = Mid$(tempStr, 3030, 12)
tempArr(1, 216) = Mid$(tempStr, 3042, 4)
tempArr(1, 217) = Mid$(tempStr, 3046, 30)
tempArr(1, 218) = Mid$(tempStr, 3076, 26)
tempArr(1, 219) = Mid$(tempStr, 3102, 26)
tempArr(1, 220) = Mid$(tempStr, 3128, 51)
tempArr(1, 221) = Mid$(tempStr, 3179, 20)
tempArr(1, 222) = Mid$(tempStr, 3199, 11)
tempArr(1, 223) = Mid$(tempStr, 3210, 12)
tempArr(1, 224) = Mid$(tempStr, 3222, 1)
tempArr(1, 225) = Mid$(tempStr, 3223, 10)
tempArr(1, 226) = Mid$(tempStr, 3233, 1)
tempArr(1, 227) = Mid$(tempStr, 3234, 1)
tempArr(1, 228) = Mid$(tempStr, 3235, 18)
tempArr(1, 229) = Mid$(tempStr, 3253, 12)
tempArr(1, 230) = Mid$(tempStr, 3265, 12)
tempArr(1, 231) = Mid$(tempStr, 3277, 3)
tempArr(1, 232) = Mid$(tempStr, 3280, 2)
tempArr(1, 233) = Mid$(tempStr, 3282, 18)
tempArr(1, 234) = Mid$(tempStr, 3300, 12)
tempArr(1, 235) = Mid$(tempStr, 3312, 11)
tempArr(1, 236) = Mid$(tempStr, 3323, 11)
tempArr(1, 237) = Mid$(tempStr, 3334, 56)
tempArr(1, 238) = Mid$(tempStr, 3390, 56)
tempArr(1, 239) = Mid$(tempStr, 3446, 74)
tempArr(1, 240) = Mid$(tempStr, 3520, 13)
tempArr(1, 241) = Mid$(tempStr, 3533, 3)
tempArr(1, 242) = Mid$(tempStr, 3536, 16)
tempArr(1, 243) = Mid$(tempStr, 3552, 4)
tempArr(1, 244) = Mid$(tempStr, 3556, 4)
tempArr(1, 245) = Mid$(tempStr, 3560, 4)
tempArr(1, 246) = Mid$(tempStr, 3564, 13)
tempArr(1, 247) = Mid$(tempStr, 3577, 18)
tempArr(1, 248) = Mid$(tempStr, 3595, 12)
tempArr(1, 249) = Mid$(tempStr, 3607, 4)
tempArr(1, 250) = Mid$(tempStr, 3611, 2)
tempArr(1, 251) = Mid$(tempStr, 3613, 2)
tempArr(1, 252) = Mid$(tempStr, 3615, 12)
tempArr(1, 253) = Mid$(tempStr, 3627, 2)
tempArr(1, 254) = Mid$(tempStr, 3629, 2)
tempArr(1, 255) = Mid$(tempStr, 3631, 16)
tempArr(1, 256) = Mid$(tempStr, 3647, 14)
tempArr(1, 257) = Mid$(tempStr, 3661, 13)
tempArr(1, 258) = Mid$(tempStr, 3674, 2)
tempArr(1, 259) = Mid$(tempStr, 3676, 16)
tempArr(1, 260) = Mid$(tempStr, 3692, 12)
tempArr(1, 261) = Mid$(tempStr, 3704, 12)
tempArr(1, 262) = Mid$(tempStr, 3715, 38)
SplitLine1 = tempArr
End Function[/vba]Matt
Matt,
Many thanks!! That will work fine. After studying the code I am not sure how to do the second line but I can certainly be greatful to you if you could do it. Also if you could comment what is happening I could follow what you are doing better. I can tweak it to get the positioning right. Thanks a lot!!! :bow::thumb
Also, when I process the file, there are two junk characters before the character of the file. This is messing up the layout. ÿ
This must be part of your file, I noticed it in your test file as well. The first character is ascii character 255 (ÿ) and the second is ascii character 254 (þ). Whatever is generating your text file is putting those characters in there.Quote:
there are two junk characters before the character of the file. This is messing up the layout
As for the code, the line that puts the code on the sheet (at least for your line one) is:[vba] Cells(i + 1, 1).Resize(1, 256) = SplitLine1(FileCont(i))[/vba] This is really the entire row, and "EntireRow" could be substituted for "Resize(1, 256)", I just left it as-is so you can see how to do line 2 (change the 256 to however many fields you have in line 2).
Here is part of SplitLine1 showing what it is doing. If you're not sure, Mid$ is the same as Mid (it just works a little faster, you use the $ one if you know that the first argument is of type string).[vba]Function SplitLine1(ByVal tempStr As String) As String()
Dim tempArr() As String 'Create array variable
ReDim tempArr(1 To 1, 1 To 262) 're-dimension array to be 1 row by 262 columns (read prior post)
tempArr(1, 1) = Mid$(tempStr, 1, 1)
tempArr(1, 2) = Mid$(tempStr, 2, 2)
tempArr(1, 3) = Mid$(tempStr, 4, 12)
'Breakdown of line like: tempArr(1, 3) = Mid$(tempStr, 4, 12)
' tempArr(1, 3) = 'enters value into array at row 1, column 3
' Mid$(tempStr, 4, 12) 'extract string starting at character 4 with length of 12
'many other similar lines
SplitLine1 = tempArr 'set function return value to be the array
End Function[/vba]
You can add something to clean up those two ascii characters at the beginning of SplitLine1 if you want, but you need to be specific about it. This will remove ascii characters 255 and 254, but if your file could have others, I would seriously look at the source of the file.[vba] tempStr = Replace(tempStr, Chr(255), "")
tempStr = Replace(tempStr, Chr(254), "")
'or combine the two onto one line
tempStr = Replace(Replace(tempStr, Chr(255), ""), Chr(254), "")[/vba]
Norie - Agreed.
Please let us know if we can help you further, Austen.
Matt
Matt,
I am very greatful for all of your help. One last question. How do I make it go to the next row or is that built in the code already? Also, I added
[VBA]tempStr = Replace(tempStr, Chr(255), " "
tempStr = Replace(tempStr, Chr(254), " "[/VBA]
At the very beginning of SplitLine1 at your suggestion. It clears out the ASCII characters but parses the first field in cell B1.
I don't know why it would start the first field into B1, that really shouldn't have happened unless you changed something else somewhere.
Also, it is built in that the first part of each line is on line 1, and the second part would need to go into a SplitLine2 function or something similar. To test this I copied the line from your test.txt file to create 4 or 5 lines of text. The first 3752 characters were parsed correctly each of the lines.
DJB-Stealthy :)
So I assume that I call the Split Line2 function right after the line that calls SplitLine1?
[VBA]
Cells(i + 1, 1).Resize(1, 256) = SplitLine1(FileCont(i)) [/VBA]
or does it go somewhere else?
Correct.. replace the "NumberOfFieldsOnLine2" with the number of fields for your line 2.[vba] Cells(i + 1, 1).Resize(1, 256) = SplitLine1(FileCont(i))
Cells(i + 2, 1).Resize(1, NumberOfFieldsOnLine2) = SplitLine2(FileCont(i + 1))[/vba]
Don't forget about the 262 field thing I told you about earlier, that will need to be fixed!
Matt
I wrote the Split Line 2 function and tried to run it with the code below. It put the remaining data in one cell.
[VBA] Sub AustenSplitFile()
Dim vFile As String, vFF As Long, tempStr As String
Dim FileCont() As String, Cnt As Long, i As Long
vFile = Application.GetOpenFilename("Text Files,*.txt,All Files,*.*")
If LCase(vFile) = "false" Then Exit Sub
vFF = FreeFile
Open vFile For Input As #vFF
Do Until EOF(vFF)
Line Input #vFF, tempStr
ReDim Preserve FileCont(Cnt + 1)
FileCont(Cnt) = Left$(tempStr, 3752)
FileCont(Cnt + 1) = Mid$(tempStr, 3753)
Cnt = Cnt + 2
Loop
Close #vFF
Application.ScreenUpdating = False
If ActiveWorkbook Is Nothing Then
Workbooks.Add -4167
Else
Sheets.Add After:=Sheets(Sheets.Count)
End If
For i = 0 To Cnt - 2 Step 2
Cells(i + 1, 1).Resize(1, 256) = SplitLine1(FileCont(i))
Cells(i + 2, 1).Resize(1, 21) = SplitLine2(FileCont(i + 1))
Cells(i + 2, 1) = FileCont(i + 1)
Next
Application.ScreenUpdating = False
End Sub
Function SplitLine1(ByVal tempStr As String) As String()
Dim tempArr() As String
tempStr = Replace(Replace(tempStr, Chr(255), ""), Chr(254), "")
ReDim tempArr(1 To 1, 1 To 256)
tempArr(1, 1) = Mid$(tempStr, 1, 1)
tempArr(1, 2) = Mid$(tempStr, 2, 2)
tempArr(1, 3) = Mid$(tempStr, 4, 12)
tempArr(1, 4) = Mid$(tempStr, 16, 35)
tempArr(1, 5) = Mid$(tempStr, 51, 25)
tempArr(1, 6) = Mid$(tempStr, 76, 25)
tempArr(1, 7) = Mid$(tempStr, 101, 10)
tempArr(1, 8) = Mid$(tempStr, 112, 4)
tempArr(1, 9) = Mid$(tempStr, 112, 4)
tempArr(1, 10) = Mid$(tempStr, 117, 1)
tempArr(1, 11) = Mid$(tempStr, 118, 11)
tempArr(1, 14) = Mid$(tempStr, 130, 11)
tempArr(1, 16) = Mid$(tempStr, 142, 11)
tempArr(1, 18) = Mid$(tempStr, 154, 11)
tempArr(1, 20) = Mid$(tempStr, 166, 11)
tempArr(1, 21) = Mid$(tempStr, 178, 11)
tempArr(1, 22) = Mid$(tempStr, 190, 11)
tempArr(1, 23) = Mid$(tempStr, 202, 11)
tempArr(1, 24) = Mid$(tempStr, 214, 3)
tempArr(1, 25) = Mid$(tempStr, 218, 3)
tempArr(1, 26) = Mid$(tempStr, 222, 3)
tempArr(1, 27) = Mid$(tempStr, 226, 3)
tempArr(1, 28) = Mid$(tempStr, 230, 3)
tempArr(1, 29) = Mid$(tempStr, 234, 3)
tempArr(1, 30) = Mid$(tempStr, 238, 3)
tempArr(1, 31) = Mid$(tempStr, 242, 3)
tempArr(1, 32) = Mid$(tempStr, 246, 11)
tempArr(1, 33) = Mid$(tempStr, 257, 10)
tempArr(1, 34) = Mid$(tempStr, 267, 10)
tempArr(1, 35) = Mid$(tempStr, 277, 11)
tempArr(1, 36) = Mid$(tempStr, 288, 2)
tempArr(1, 37) = Mid$(tempStr, 290, 12)
tempArr(1, 38) = Mid$(tempStr, 302, 12)
tempArr(1, 39) = Mid$(tempStr, 314, 25)
tempArr(1, 40) = Mid$(tempStr, 339, 2)
tempArr(1, 41) = Mid$(tempStr, 341, 12)
tempArr(1, 42) = Mid$(tempStr, 353, 8)
tempArr(1, 43) = Mid$(tempStr, 362, 15)
tempArr(1, 44) = Mid$(tempStr, 377, 5)
tempArr(1, 45) = Mid$(tempStr, 382, 5)
tempArr(1, 46) = Mid$(tempStr, 387, 1)
tempArr(1, 47) = Mid$(tempStr, 388, 1)
tempArr(1, 48) = Mid$(tempStr, 389, 25)
tempArr(1, 49) = Mid$(tempStr, 414, 10)
tempArr(1, 50) = Mid$(tempStr, 424, 12)
tempArr(1, 51) = Mid$(tempStr, 436, 10)
tempArr(1, 52) = Mid$(tempStr, 446, 26)
tempArr(1, 53) = Mid$(tempStr, 472, 3)
tempArr(1, 54) = Mid$(tempStr, 476, 11)
tempArr(1, 55) = Mid$(tempStr, 487, 1)
tempArr(1, 56) = Mid$(tempStr, 488, 3)
tempArr(1, 57) = Mid$(tempStr, 491, 1)
tempArr(1, 58) = Mid$(tempStr, 492, 1)
tempArr(1, 59) = Mid$(tempStr, 493, 2)
tempArr(1, 60) = Mid$(tempStr, 495, 1)
tempArr(1, 61) = Mid$(tempStr, 497, 1)
tempArr(1, 62) = Mid$(tempStr, 499, 1)
tempArr(1, 63) = Mid$(tempStr, 501, 1)
tempArr(1, 64) = Mid$(tempStr, 503, 1)
tempArr(1, 65) = Mid$(tempStr, 504, 1)
tempArr(1, 66) = Mid$(tempStr, 507, 2)
tempArr(1, 67) = Mid$(tempStr, 509, 1)
tempArr(1, 68) = Mid$(tempStr, 511, 1)
tempArr(1, 69) = Mid$(tempStr, 513, 1)
tempArr(1, 70) = Mid$(tempStr, 515, 12)
tempArr(1, 71) = Mid$(tempStr, 528, 12)
tempArr(1, 72) = Mid$(tempStr, 541, 12)
tempArr(1, 73) = Mid$(tempStr, 554, 12)
tempArr(1, 74) = Mid$(tempStr, 567, 12)
tempArr(1, 75) = Mid$(tempStr, 580, 2)
tempArr(1, 76) = Mid$(tempStr, 583, 3)
tempArr(1, 77) = Mid$(tempStr, 587, 1)
tempArr(1, 78) = Mid$(tempStr, 589, 1)
tempArr(1, 79) = Mid$(tempStr, 591, 1)
tempArr(1, 80) = Mid$(tempStr, 593, 1)
tempArr(1, 81) = Mid$(tempStr, 595, 1)
tempArr(1, 82) = Mid$(tempStr, 597, 1)
tempArr(1, 83) = Mid$(tempStr, 599, 1)
tempArr(1, 84) = Mid$(tempStr, 601, 1)
tempArr(1, 85) = Mid$(tempStr, 603, 1)
tempArr(1, 86) = Mid$(tempStr, 605, 2)
tempArr(1, 87) = Mid$(tempStr, 608, 50)
tempArr(1, 88) = Mid$(tempStr, 659, 10)
tempArr(1, 89) = Mid$(tempStr, 670, 10)
tempArr(1, 90) = Mid$(tempStr, 681, 17)
tempArr(1, 91) = Mid$(tempStr, 699, 50)
tempArr(1, 92) = Mid$(tempStr, 750, 55)
tempArr(1, 93) = Mid$(tempStr, 806, 55)
tempArr(1, 94) = Mid$(tempStr, 862, 55)
tempArr(1, 95) = Mid$(tempStr, 918, 30)
tempArr(1, 96) = Mid$(tempStr, 949, 2)
tempArr(1, 97) = Mid$(tempStr, 952, 15)
tempArr(1, 98) = Mid$(tempStr, 968, 16)
tempArr(1, 99) = Mid$(tempStr, 985, 35)
tempArr(1, 100) = Mid$(tempStr, 1021, 25)
tempArr(1, 101) = Mid$(tempStr, 1047, 25)
tempArr(1, 102) = Mid$(tempStr, 1073, 50)
tempArr(1, 103) = Mid$(tempStr, 1124, 10)
tempArr(1, 104) = Mid$(tempStr, 1135, 10)
tempArr(1, 105) = Mid$(tempStr, 1148, 17)
tempArr(1, 106) = Mid$(tempStr, 1164, 50)
tempArr(1, 107) = Mid$(tempStr, 1215, 55)
tempArr(1, 108) = Mid$(tempStr, 1271, 55)
tempArr(1, 109) = Mid$(tempStr, 1327, 55)
tempArr(1, 110) = Mid$(tempStr, 1383, 30)
tempArr(1, 111) = Mid$(tempStr, 1414, 2)
tempArr(1, 112) = Mid$(tempStr, 1417, 15)
tempArr(1, 113) = Mid$(tempStr, 1433, 16)
tempArr(1, 114) = Mid$(tempStr, 1450, 25)
tempArr(1, 115) = Mid$(tempStr, 1476, 2)
tempArr(1, 116) = Mid$(tempStr, 1479, 35)
tempArr(1, 117) = Mid$(tempStr, 1515, 25)
tempArr(1, 118) = Mid$(tempStr, 1541, 25)
tempArr(1, 119) = Mid$(tempStr, 1567, 55)
tempArr(1, 120) = Mid$(tempStr, 1623, 55)
tempArr(1, 121) = Mid$(tempStr, 1679, 55)
tempArr(1, 122) = Mid$(tempStr, 1735, 30)
tempArr(1, 123) = Mid$(tempStr, 1766, 2)
tempArr(1, 124) = Mid$(tempStr, 1769, 15)
tempArr(1, 125) = Mid$(tempStr, 1785, 3)
tempArr(1, 126) = Mid$(tempStr, 1789, 3)
tempArr(1, 127) = Mid$(tempStr, 1793, 80)
tempArr(1, 128) = Mid$(tempStr, 1874, 14)
tempArr(1, 129) = Mid$(tempStr, 1889, 14)
tempArr(1, 130) = Mid$(tempStr, 1904, 10)
tempArr(1, 131) = Mid$(tempStr, 1915, 1)
tempArr(1, 132) = Mid$(tempStr, 1916, 80)
tempArr(1, 133) = Mid$(tempStr, 1997, 2)
tempArr(1, 134) = Mid$(tempStr, 2000, 50)
tempArr(1, 135) = Mid$(tempStr, 2051, 25)
tempArr(1, 136) = Mid$(tempStr, 2077, 25)
tempArr(1, 137) = Mid$(tempStr, 2103, 55)
tempArr(1, 138) = Mid$(tempStr, 2159, 55)
tempArr(1, 139) = Mid$(tempStr, 2215, 55)
tempArr(1, 140) = Mid$(tempStr, 2271, 30)
tempArr(1, 141) = Mid$(tempStr, 2302, 2)
tempArr(1, 142) = Mid$(tempStr, 2305, 15)
tempArr(1, 143) = Mid$(tempStr, 2321, 3)
tempArr(1, 144) = Mid$(tempStr, 2329, 81)
tempArr(1, 145) = Mid$(tempStr, 2325, 3)
tempArr(1, 146) = Mid$(tempStr, 2329, 80)
tempArr(1, 147) = Mid$(tempStr, 2410, 14)
tempArr(1, 148) = Mid$(tempStr, 2424, 10)
tempArr(1, 149) = Mid$(tempStr, 2436, 11)
tempArr(1, 150) = Mid$(tempStr, 2448, 3)
tempArr(1, 151) = Mid$(tempStr, 2452, 11)
tempArr(1, 151) = Mid$(tempStr, 2464, 3)
tempArr(1, 152) = Mid$(tempStr, 2468, 11)
tempArr(1, 153) = Mid$(tempStr, 2480, 3)
tempArr(1, 154) = Mid$(tempStr, 2484, 11)
tempArr(1, 155) = Mid$(tempStr, 2496, 3)
tempArr(1, 156) = Mid$(tempStr, 2500, 8)
tempArr(1, 157) = Mid$(tempStr, 2509, 1)
tempArr(1, 158) = Mid$(tempStr, 2511, 4)
tempArr(1, 159) = Mid$(tempStr, 2516, 8)
tempArr(1, 160) = Mid$(tempStr, 2525, 11)
tempArr(1, 161) = Mid$(tempStr, 2537, 9)
tempArr(1, 162) = Mid$(tempStr, 2547, 3)
tempArr(1, 163) = Mid$(tempStr, 2551, 1)
tempArr(1, 164) = Mid$(tempStr, 2553, 1)
tempArr(1, 165) = Mid$(tempStr, 2555, 2)
tempArr(1, 166) = Mid$(tempStr, 2558, 2)
tempArr(1, 167) = Mid$(tempStr, 2561, 1)
tempArr(1, 168) = Mid$(tempStr, 2563, 13)
tempArr(1, 169) = Mid$(tempStr, 2577, 50)
tempArr(1, 170) = Mid$(tempStr, 2628, 55)
tempArr(1, 171) = Mid$(tempStr, 2684, 55)
tempArr(1, 172) = Mid$(tempStr, 2740, 55)
tempArr(1, 173) = Mid$(tempStr, 2796, 30)
tempArr(1, 174) = Mid$(tempStr, 2827, 2)
tempArr(1, 175) = Mid$(tempStr, 2830, 15)
tempArr(1, 176) = Mid$(tempStr, 2846, 4)
tempArr(1, 177) = Mid$(tempStr, 2851, 5)
tempArr(1, 178) = Mid$(tempStr, 2857, 15)
tempArr(1, 179) = Mid$(tempStr, 2873, 15)
tempArr(1, 180) = Mid$(tempStr, 2889, 5)
tempArr(1, 181) = Mid$(tempStr, 2895, 1)
tempArr(1, 182) = Mid$(tempStr, 2897, 1)
tempArr(1, 183) = Mid$(tempStr, 2899, 1)
tempArr(1, 184) = Mid$(tempStr, 2901, 11)
tempArr(1, 185) = Mid$(tempStr, 2913, 1)
tempArr(1, 186) = Mid$(tempStr, 2915, 11)
tempArr(1, 187) = Mid$(tempStr, 2927, 11)
tempArr(1, 188) = Mid$(tempStr, 2939, 11)
tempArr(1, 189) = Mid$(tempStr, 2951, 1)
tempArr(1, 190) = Mid$(tempStr, 2953, 11)
tempArr(1, 191) = Mid$(tempStr, 2965, 1)
tempArr(1, 192) = Mid$(tempStr, 2966, 1)
tempArr(1, 193) = Mid$(tempStr, 2968, 1)
tempArr(1, 194) = Mid$(tempStr, 2970, 1)
tempArr(1, 195) = Mid$(tempStr, 2972, 1)
tempArr(1, 196) = Mid$(tempStr, 2974, 1)
tempArr(1, 197) = Mid$(tempStr, 2976, 1)
tempArr(1, 198) = Mid$(tempStr, 2978, 1)
tempArr(1, 199) = Mid$(tempStr, 2980, 1)
tempArr(1, 200) = Mid$(tempStr, 2982, 1)
tempArr(1, 201) = Mid$(tempStr, 2984, 1)
tempArr(1, 202) = Mid$(tempStr, 2986, 1)
tempArr(1, 203) = Mid$(tempStr, 2989, 2)
tempArr(1, 204) = Mid$(tempStr, 2991, 2)
tempArr(1, 205) = Mid$(tempStr, 2993, 2)
tempArr(1, 206) = Mid$(tempStr, 2995, 2)
tempArr(1, 207) = Mid$(tempStr, 2997, 2)
tempArr(1, 208) = Mid$(tempStr, 2999, 12)
tempArr(1, 209) = Mid$(tempStr, 3012, 12)
tempArr(1, 210) = Mid$(tempStr, 3025, 1)
tempArr(1, 211) = Mid$(tempStr, 3027, 2)
tempArr(1, 212) = Mid$(tempStr, 3030, 12)
tempArr(1, 213) = Mid$(tempStr, 3042, 3)
tempArr(1, 214) = Mid$(tempStr, 3046, 29)
tempArr(1, 215) = Mid$(tempStr, 3076, 25)
tempArr(1, 216) = Mid$(tempStr, 3102, 25)
tempArr(1, 217) = Mid$(tempStr, 3128, 50)
tempArr(1, 218) = Mid$(tempStr, 3179, 17)
tempArr(1, 219) = Mid$(tempStr, 3197, 1)
tempArr(1, 220) = Mid$(tempStr, 3199, 11)
tempArr(1, 221) = Mid$(tempStr, 3211, 11)
tempArr(1, 222) = Mid$(tempStr, 3223, 10)
tempArr(1, 223) = Mid$(tempStr, 3233, 1)
tempArr(1, 224) = Mid$(tempStr, 3234, 1)
tempArr(1, 225) = Mid$(tempStr, 3235, 17)
tempArr(1, 226) = Mid$(tempStr, 3253, 11)
tempArr(1, 227) = Mid$(tempStr, 3265, 11)
tempArr(1, 228) = Mid$(tempStr, 3277, 1)
tempArr(1, 229) = Mid$(tempStr, 3279, 2)
tempArr(1, 230) = Mid$(tempStr, 3282, 17)
tempArr(1, 231) = Mid$(tempStr, 3300, 11)
tempArr(1, 232) = Mid$(tempStr, 3312, 10)
tempArr(1, 233) = Mid$(tempStr, 3323, 10)
tempArr(1, 234) = Mid$(tempStr, 3334, 55)
tempArr(1, 235) = Mid$(tempStr, 3390, 55)
tempArr(1, 236) = Mid$(tempStr, 3446, 55)
tempArr(1, 237) = Mid$(tempStr, 3502, 30)
tempArr(1, 238) = Mid$(tempStr, 3533, 2)
tempArr(1, 239) = Mid$(tempStr, 3536, 15)
tempArr(1, 240) = Mid$(tempStr, 3552, 3)
tempArr(1, 241) = Mid$(tempStr, 3556, 3)
tempArr(1, 242) = Mid$(tempStr, 3560, 3)
tempArr(1, 243) = Mid$(tempStr, 3564, 12)
tempArr(1, 244) = Mid$(tempStr, 3577, 17)
tempArr(1, 245) = Mid$(tempStr, 3595, 11)
tempArr(1, 246) = Mid$(tempStr, 3607, 3)
tempArr(1, 247) = Mid$(tempStr, 3577, 18)
tempArr(1, 248) = Mid$(tempStr, 3611, 1)
tempArr(1, 249) = Mid$(tempStr, 3613, 1)
tempArr(1, 250) = Mid$(tempStr, 3615, 11)
tempArr(1, 251) = Mid$(tempStr, 3627, 1)
tempArr(1, 252) = Mid$(tempStr, 3629, 1)
tempArr(1, 253) = Mid$(tempStr, 3631, 15)
SplitLine1 = tempArr
End Function
Function SplitLine2(ByVal tempStr As String) As String()
Dim tempArr() As String
tempStr = Replace(Replace(tempStr, Chr(255), ""), Chr(254), "")
ReDim tempArr(1 To 1, 1 To 21)
tempArr(1, 1) = Mid$(tempStr, 3647, 13)
tempArr(1, 2) = Mid$(tempStr, 3661, 12)
tempArr(1, 3) = Mid$(tempStr, 3674, 1)
tempArr(1, 4) = Mid$(tempStr, 3676, 15)
tempArr(1, 5) = Mid$(tempStr, 3692, 11)
tempArr(1, 6) = Mid$(tempStr, 3704, 11)
tempArr(1, 7) = Mid$(tempStr, 3716, 11)
tempArr(1, 8) = Mid$(tempStr, 3728, 11)
tempArr(1, 9) = Mid$(tempStr, 3740, 11)
tempArr(1, 10) = Mid$(tempStr, 3752, 20)
tempArr(1, 11) = Mid$(tempStr, 3773, 12)
tempArr(1, 12) = Mid$(tempStr, 3786, 5)
tempArr(1, 13) = Mid$(tempStr, 3792, 3)
tempArr(1, 14) = Mid$(tempStr, 3796, 1)
tempArr(1, 15) = Mid$(tempStr, 3798, 11)
tempArr(1, 16) = Mid$(tempStr, 3810, 14)
tempArr(1, 17) = Mid$(tempStr, 3825, 14)
tempArr(1, 18) = Mid$(tempStr, 3840, 1)
tempArr(1, 19) = Mid$(tempStr, 3842, 5)
tempArr(1, 20) = Mid$(tempStr, 3848, 2)
tempArr(1, 21) = Mid$(tempStr, 3851, 5)
SplitLine2 = tempArr
End Function
[/VBA]
Any ideas?
Remove this line:[vba]Cells(i + 2, 1) = FileCont(i + 1)[/vba]Directly below where you're caling SplitLine2, should take care of it
Hi Matt,
Now row 2 is blank with the above line removed.
Hi Austen,
You're right, it is. Keep that line out, I just looked at your SplitLine2 function a little closer.
The string being sent to it is not the 3856 character line, it is the line from character 3753 and on. So instead of using something like[vba] tempArr(1, 1) = Mid$(tempStr, 3647, 13)
tempArr(1, 2) = Mid$(tempStr, 3661, 12)[/vba]Instead use:[vba] tempArr(1, 1) = Mid$(tempStr, 1, 13)
tempArr(1, 2) = Mid$(tempStr, 14, 12)
'etc[/vba]I didn't look closely enough yesterday, sorry about that.
Matt