Text to Column with fixed width

[kathy.aubin]

I have text file with about 300 lines items that are not delimited but
the width of each columns is fixed and do have the value. For example,
cell 1 is char(1-2), cell 2 is char(2-14)....
I want to be able to copy the whole thing in Excel, run a macro that
split the string into the different cell.
I can manually do it using the Text to Column function and setting the
width one by one but it's time consuming since this import is occuring
everyday.

Can you help?

Thanks,

 

[Allen Browne]

If you want to split the data into multiple columns before you send it to
Excel, you could do that in a query, and export the query.

For example, to generate a column named C1 from the first 2 characters in a
field named f, type an expression like this into the Field row in query
design
C1: Trim(Left([f],2))

Then in the next column of the Field row:
C2: Trim(Mid([f],3,12))

And so on.

 

[John W. Vinson]

I have text file with about 300 lines items that are not delimited but
the width of each columns is fixed and do have the value. For example,
cell 1 is char(1-2), cell 2 is char(2-14)....
I want to be able to copy the whole thing in Excel, run a macro that
split the string into the different cell.
I can manually do it using the Text to Column function and setting the
width one by one but it's time consuming since this import is occuring
everyday.

I'd (strongly!!!) suggest setting up and storing a File Import Specification
(using the Advanced button on the import wizard). You can map the character
positions to fieldnames. You don't need to do this in Excel - it can be done
directly in the import.

Take a look at the Help for "Import or Link Data and Objects" or go a Google
Groups search of these newsgroups for "Import Specification". I was noodling
around in the Help file and the Microsoft Knowledge Base but wasn't able to
quickly find a detailed description, but I know it exists somewhere... :-{(



John W. Vinson [MVP]