Hi All,

I have a text file I'm working with that I need to break into separate
columns. The only problem is, I cannot use the standard Text to Columns since
the text file has some data separated by spacing and other "supposed to be"
separate pieces are clumped together in one string. The formula below is what
I'd like to base a macro on. It's setup exactly where the columns/strings
should break and I'm just looking how best to structure it into a Next If
based on the below settings. This would also be for the whole column of A
vs. just A1 as presented in the example.

Thanks for your review and thoughts - Jenny B.

=LEFT(A1,60)&" "&MID(A1,61,9)&" "&MID(A1,71,4)&" "&MID(A1,76,5)&"
"&MID(A1,81,12)&"."&""&MID(A1,94,3)&" "&MID(A1,97,9)&" "&MID(A1,106,2)&"
"&MID(A1,108,6)&" "&MID(A1,114,6)&" "&MID(A1,120,10)&" "&MID(A1,130,10)&"
"&MID(A1,140,2)&" "&MID(A1,142,8)&" "&MID(A1,148,8)&" "&MID(A1,156,30)&"
"&MID(A1,196,40)&" "&MID(A1,236,40)&" "&MID(A1,276,40)&" "&MID(A1,316,40)&"
"&MID(A1,356,15)&" "&RIGHT(A1,2)

Hi Jenny

Here's the first part:

Range("B1:B100").Formula = "=LEFT(RC[-1],60)&"" ""&MID(RC[-1],61,9)"

Continue in the same fashion.

Adjust the row 100 as well (this could also be done with code of course).


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Jenny B." wrote:

Hi All,

I have a text file I'm working with that I need to break into separate
columns. The only problem is, I cannot use the standard Text to Columns since
the text file has some data separated by spacing and other "supposed to be"
separate pieces are clumped together in one string. The formula below is what
I'd like to base a macro on. It's setup exactly where the columns/strings
should break and I'm just looking how best to structure it into a Next If
based on the below settings. This would also be for the whole column of A
vs. just A1 as presented in the example.

Thanks for your review and thoughts - Jenny B.

=LEFT(A1,60)&" "&MID(A1,61,9)&" "&MID(A1,71,4)&" "&MID(A1,76,5)&"
"&MID(A1,81,12)&"."&""&MID(A1,94,3)&" "&MID(A1,97,9)&" "&MID(A1,106,2)&"
"&MID(A1,108,6)&" "&MID(A1,114,6)&" "&MID(A1,120,10)&" "&MID(A1,130,10)&"
"&MID(A1,140,2)&" "&MID(A1,142,8)&" "&MID(A1,148,8)&" "&MID(A1,156,30)&"
"&MID(A1,196,40)&" "&MID(A1,236,40)&" "&MID(A1,276,40)&" "&MID(A1,316,40)&"
"&MID(A1,356,15)&" "&RIGHT(A1,2)

change FName as required.

Sub Macro1()
'
'
FName = "C:\TEMP\suppliers.txt"
'
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FName, _
Destination:=Range("A1"))

.Name = "ABC"
.AdjustColumnWidth = True
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileFixedColumnWidths = _
Array(60, 9, 4, 5, 12, 3, 9, 2, 6, 6, 10, 10, 2, 8, _
8, 30, 40, 40, 40, 40, 15, 2)
.Refresh BackgroundQuery:=False
End With
End Sub


"Jenny B." wrote:

Hi All,

I have a text file I'm working with that I need to break into separate
columns. The only problem is, I cannot use the standard Text to Columns since
the text file has some data separated by spacing and other "supposed to be"
separate pieces are clumped together in one string. The formula below is what
I'd like to base a macro on. It's setup exactly where the columns/strings
should break and I'm just looking how best to structure it into a Next If
based on the below settings. This would also be for the whole column of A
vs. just A1 as presented in the example.

Thanks for your review and thoughts - Jenny B.

=LEFT(A1,60)&" "&MID(A1,61,9)&" "&MID(A1,71,4)&" "&MID(A1,76,5)&"
"&MID(A1,81,12)&"."&""&MID(A1,94,3)&" "&MID(A1,97,9)&" "&MID(A1,106,2)&"
"&MID(A1,108,6)&" "&MID(A1,114,6)&" "&MID(A1,120,10)&" "&MID(A1,130,10)&"
"&MID(A1,140,2)&" "&MID(A1,142,8)&" "&MID(A1,148,8)&" "&MID(A1,156,30)&"
"&MID(A1,196,40)&" "&MID(A1,236,40)&" "&MID(A1,276,40)&" "&MID(A1,316,40)&"
"&MID(A1,356,15)&" "&RIGHT(A1,2)

Hi Joel,

Thanks so much for simplifying my formula. This works great and I like the
fact that it imports direct from the Text Table vs. having to paste it in the
Excel and then run the Macro to divide out the columns.

Take care and thanks so much for the great idea - Jenny B.


"Joel" wrote:

change FName as required.

Sub Macro1()
'
'
FName = "C:\TEMP\suppliers.txt"
'
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FName, _
Destination:=Range("A1"))

.Name = "ABC"
.AdjustColumnWidth = True
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileFixedColumnWidths = _
Array(60, 9, 4, 5, 12, 3, 9, 2, 6, 6, 10, 10, 2, 8, _
8, 30, 40, 40, 40, 40, 15, 2)
.Refresh BackgroundQuery:=False
End With
End Sub


"Jenny B." wrote:

Hi All,

I have a text file I'm working with that I need to break into separate
columns. The only problem is, I cannot use the standard Text to Columns since
the text file has some data separated by spacing and other "supposed to be"
separate pieces are clumped together in one string. The formula below is what
I'd like to base a macro on. It's setup exactly where the columns/strings
should break and I'm just looking how best to structure it into a Next If
based on the below settings. This would also be for the whole column of A
vs. just A1 as presented in the example.

Thanks for your review and thoughts - Jenny B.

=LEFT(A1,60)&" "&MID(A1,61,9)&" "&MID(A1,71,4)&" "&MID(A1,76,5)&"
"&MID(A1,81,12)&"."&""&MID(A1,94,3)&" "&MID(A1,97,9)&" "&MID(A1,106,2)&"
"&MID(A1,108,6)&" "&MID(A1,114,6)&" "&MID(A1,120,10)&" "&MID(A1,130,10)&"
"&MID(A1,140,2)&" "&MID(A1,142,8)&" "&MID(A1,148,8)&" "&MID(A1,156,30)&"
"&MID(A1,196,40)&" "&MID(A1,236,40)&" "&MID(A1,276,40)&" "&MID(A1,316,40)&"
"&MID(A1,356,15)&" "&RIGHT(A1,2)

What I did is really Text-To-Columns using Fixed width instead of delimited.
Text-To-Column and Import External data uses the same internal excel
functions to read data and seperate the data into columns.

"Jenny B." wrote:

Hi Joel,

Thanks so much for simplifying my formula. This works great and I like the
fact that it imports direct from the Text Table vs. having to paste it in the
Excel and then run the Macro to divide out the columns.

Take care and thanks so much for the great idea - Jenny B.


"Joel" wrote:

change FName as required.

Sub Macro1()
'
'
FName = "C:\TEMP\suppliers.txt"
'
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FName, _
Destination:=Range("A1"))

.Name = "ABC"
.AdjustColumnWidth = True
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileFixedColumnWidths = _
Array(60, 9, 4, 5, 12, 3, 9, 2, 6, 6, 10, 10, 2, 8, _
8, 30, 40, 40, 40, 40, 15, 2)
.Refresh BackgroundQuery:=False
End With
End Sub


"Jenny B." wrote:

Hi All,

I have a text file I'm working with that I need to break into separate
columns. The only problem is, I cannot use the standard Text to Columns since
the text file has some data separated by spacing and other "supposed to be"
separate pieces are clumped together in one string. The formula below is what
I'd like to base a macro on. It's setup exactly where the columns/strings
should break and I'm just looking how best to structure it into a Next If
based on the below settings. This would also be for the whole column of A
vs. just A1 as presented in the example.

Thanks for your review and thoughts - Jenny B.

=LEFT(A1,60)&" "&MID(A1,61,9)&" "&MID(A1,71,4)&" "&MID(A1,76,5)&"
"&MID(A1,81,12)&"."&""&MID(A1,94,3)&" "&MID(A1,97,9)&" "&MID(A1,106,2)&"
"&MID(A1,108,6)&" "&MID(A1,114,6)&" "&MID(A1,120,10)&" "&MID(A1,130,10)&"
"&MID(A1,140,2)&" "&MID(A1,142,8)&" "&MID(A1,148,8)&" "&MID(A1,156,30)&"
"&MID(A1,196,40)&" "&MID(A1,236,40)&" "&MID(A1,276,40)&" "&MID(A1,316,40)&"
"&MID(A1,356,15)&" "&RIGHT(A1,2)