Notepad data to Excel WB with manipulation

Hello Everyone,

I'm very much in the novice bracket when it comes to macros and excel.

I'm looking for a way to automate something I'm doing manually at present. I hope someone out there will be able to point me in the right direction.

I have a .txt file (attached) that I would like to construct a macro for. The objective of the macro is to import the 3 sets of numbers into an excel workbook. The first set would be imported into column B, starting at row 10. The second set into column C row 10 and the third into column D row 10. The data sets will be of different lengths. The last row of each column will hold the calculated average of that column.
I also wish to put column headings into row 9. These will be static and so can be hard programmed.

I hope that someone can steer me in the right direction.


With Thanks

This code is a "Tweak" of http://www.cpearson.com/excel/imptext.htm

Please Login or Register  to view this content.

This uses the same basic structure as Nimrod's solution but a different method for extracting the data:

Please Login or Register  to view this content.

You're welcome. Thanks for the feedback.

Please can you mark the thread as solved.

Regards

the simplest method to import a txt-file

Please Login or Register  to view this content.

1=Tab
2=comma
3=space
4=semicolon
5=nothing
If you use a userdefined delimiter e.g. the pipeline |

Please Login or Register  to view this content.

If you use the method Open you can read the content of the file in 1 go.

Please Login or Register  to view this content.

If you want to split the cells according to the 'fieldseparator" you can use 'texttocolumns'.
In this example the separator is |

Please Login or Register  to view this content.