But you didn't describe each field for Format 2 and format 3.

Maybe you could base it on the sum of the lengths of the fields (format 1 has a
combined length of 36 characters).

If this sounds interesting,
can any of the fields be empty?
can any of the fields be shorter than the given length (can field 3 of format 1
be 1-12 characters)?

Maybe you could just add another column of cells that determine the format
type. Then use that in other formulas to parse each field:

Column B:
=IF(LEN(A1)=36,1,IF(LEN(A1)=59,2,3))
(whatever the lengths may be...)

Then in column C, grab the first field:
=IF(B1=1,TRIM(MID(A1,1,3)),"somethingelse")

in column D, grab the second field:
=IF(B1=1,TRIM(MID(A1,4,20)),"somethingelse")

and so forth.

If you can figure out a formula that goes in column B, it should work!


Rytrilius wrote:

Hi all,

I have limited experience with VBA, so I decided to seek some help here.

I need to import a text file into Excel, it does not contain commas, nor
special characters. I have 3 different layouts given to me by the user, for
example

Format 1:
Field 1. 3 chars
Field 2. 20 chars
Field 3. 13 chars

So the 3 different layouts have different column widths. The text file is
such that it loops from Format 1 (1 line) to Format 2 (20++ lines) to Format
3 (10+ lines) and then back to Format 1.
The only way to identify which format each line belongs to, is to search for
certain positions where certain text are present within the line. for example,
format 1 contains non-empty char in position 3 to 10, format 2 contains NULL
char in positions 14 to 30, format 3 contains 9s from postion 25 to 29...

I've recorded the macro to do the importing, but cannot seem to find the code
to tweak the varying column widths. any help is greatly appreciated!!

--

Dave Peterson