Special Text to Column question

[notgonna]

I have a special problem that I hope the gurus here can solve.

Once a month or so, I receive a text file that includes about 400
lines of 323 characters each. Groups of characters have different
meaning depending on their position in the string. Some are grouped
in as little as one character, other groups are as long as 25
characters. There are no delimiting characters like commas, spaces or
the like

I usually open the file in Excel and click Text to Columns and select
Fixed Width. From there I scroll left to right and click on the
necessary widths for the groups involved "9, 5, 2, 2, 1, 25, etc"

My question is this: Is there a way to run the text to column
function using existing dilimitation. I've tried pasting into the
existing sheet, but can't figure out a way to bypass the above
operation.

I am using Excel 2010.

Thanks in advance for any assistance.

 

[GS]

I have a special problem that I hope the gurus here can solve.

Once a month or so, I receive a text file that includes about 400
lines of 323 characters each. Groups of characters have different
meaning depending on their position in the string. Some are grouped
in as little as one character, other groups are as long as 25
characters. There are no delimiting characters like commas, spaces or
the like

I usually open the file in Excel and click Text to Columns and select
Fixed Width. From there I scroll left to right and click on the
necessary widths for the groups involved "9, 5, 2, 2, 1, 25, etc"

My question is this: Is there a way to run the text to column
function using existing dilimitation. I've tried pasting into the
existing sheet, but can't figure out a way to bypass the above
operation.

I am using Excel 2010.

Thanks in advance for any assistance.

Check out the Mid$() function. It will allow yo to specify starting
position and length (#characters).

 

[GS]

Ron Rosenfeld brought next idea :

You might be able to use a macro. It is hard to recommend one completely as
I don't know enough about your data, but if you place a macro like below into
a regular module, and import the data into column A; the macro will parse the
column into your predetermined column widths, treating each column as
different types of data (if necessary), just like the Text To Columns wizard
does.

As written, it preserves column A and starts the parsing in column B; but
once you get it debugged, you could overwrite column A. Note that you will
have to set up the FieldInfo parameter -- I just used what I did for testing.

You might record a macro while you are doing the Text To Columns manually.
That should also give you something to work from.

This should, at least, get you started. Note that there are many possible
ways to set up the range to parse; as written, it selects everything in
Column "A".

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the
macro by name, and <RUN>.

===========================
Option Explicit
Sub TTCSpecial()
Dim rg As Range
Set rg = Range("A1", Cells(Cells.Rows.Count, "A").End(xlUp))

rg.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
fieldinfo:=Array(Array(0, xlGeneralFormat), Array(9, xlTextFormat), _
Array(15, xlTextFormat), Array(18, xlTextFormat), Array(20,
xlTextFormat))

End Sub
=============================

Very nice, Ron! Didn't think to use TextToColumns because I've never
used it. Thanks for posting this!

 

[GS]

Ron Rosenfeld has brought this to us :

I think the key point is to do the splitting within a macro that is stored
either in the workbook itself or, if a different workbook is generated each
month, possibly in an add-in or personal.xls file.

The Text-to-columns method should be faster than looping through the range
and populating each target cell using the Mid function.

I agree!

My suggestion for using Mid$() was based entirely on my lack of knowing
about TextToColumns. I prefer to use built-in functions over VBA
whenever possible because I do believe that the built-in functions are
faster and more efficient than a VBA dupe (generally speaking).

Thanks for the feedback...