I need to write a macro to import a text file. The file is space delimited,
but has the last column on line 2. The file will look like this:

column1 column2 column3 column4 column5
column6
column1 column2 column3 column4 column5
column6

That 6th column will run almost the entire length of the previous 5. Is
there a way to import this as the 6th column instead of having it split from
the first line of data? Any help with this would be appreciated. Thank you!

If the total number of lines (including the column6 line) fit in a worksheet,
I'd just import the data and fix it after the import.

I'd put this in F1 (and drag down):
=IF(MOD(ROW(),2)=1,A2,NA())

Then select column F
edit|copy
Edit|paste special|Values

with column F still selected
edit|goto|special|Constants and uncheck Numbers, Text, Logicals--but leave
Errors checked)

Then rightclick on one of the selected N/A's and choose delete|entire row.



Jasmine wrote:

I need to write a macro to import a text file. The file is space delimited,
but has the last column on line 2. The file will look like this:

column1 column2 column3 column4 column5
column6
column1 column2 column3 column4 column5
column6

That 6th column will run almost the entire length of the previous 5. Is
there a way to import this as the 6th column instead of having it split from
the first line of data? Any help with this would be appreciated. Thank you!

--

Dave Peterson

And to be totally honest, deleting the rows that have #n/a in them using this
technique may not work (if you have over 16k rows of data (8k separate areas).

You could sort the data by that column, then delete the #n/a rows pretty quickly
though.

And if the original sort order is important....

Add another helper column (column G???) and put:
=row()
and drag down
select column G
edit|copy
edit|paste special|values

Then sort your data by column F, delete the N/A's and resort by column G. (And
delete column G when you're done.)

Dave Peterson wrote:

If the total number of lines (including the column6 line) fit in a worksheet,
I'd just import the data and fix it after the import.

I'd put this in F1 (and drag down):
=IF(MOD(ROW(),2)=1,A2,NA())

Then select column F
edit|copy
Edit|paste special|Values

with column F still selected
edit|goto|special|Constants and uncheck Numbers, Text, Logicals--but leave
Errors checked)

Then rightclick on one of the selected N/A's and choose delete|entire row.

Jasmine wrote:

I need to write a macro to import a text file. The file is space delimited,
but has the last column on line 2. The file will look like this:

column1 column2 column3 column4 column5
column6
column1 column2 column3 column4 column5
column6

That 6th column will run almost the entire length of the previous 5. Is
there a way to import this as the 6th column instead of having it split from
the first line of data? Any help with this would be appreciated. Thank you!

--

Dave Peterson

--

Dave Peterson