Organize large space delimited text file into appropriate rows and columns in excel.

[rdlaner]

I have collected data into a large text file such that each value is space delimited. I would like to import this text file into Excel in order to analyze it better. I have pasted a portion of the text file into Excel in order to attach it to this ticket. Whether I copy and paste the data or import it, I don't care, just as long as I can organize it in Excel.

I need the data to be organized into 5 different columns with the first value going into A1, the second value into B1, and continuing until the fifth value is in E1. Once there, it needs to repeat so that the sixth value is placed in A2, the seventh in B2 until the the tenth value is in E2.

For example, if I had this series of data:
0053 02A3 0000 0000 0000 0054 02A3 0000 0000 0000 0055 02A3 0000 0000 0000

It would need to be organized like this, with each value in it's respective row and column:
0053 02A3 0000 0000 0000
0054 02A3 0000 0000 0000
0055 02A3 0000 0000 0000

I hope that makes sense, let me know if you need any clarification.
Thanks!

[JBeaucaire]

Your explanation of the need is fine. If you provide a sample of your TEXT file, that would be better.

[JBeaucaire]

This seems to be pretty fast with the file created from the text in your sample file.

Please Login or Register  to view this content.

[rdlaner]

I've attached the .txt file, although I had to reduce its size so that the uploader would accept it.

[rdlaner]

Thanks for the help!
I am running into a few errors while trying to implement the code you gave me. Forgive me since I do not have much experience using macros.
When I'm in the macro editor and I try to run the script you gave me, I get this error:
"Run-time error '1004'
Application-defined or object-defined error"

I also tried assigning this macro to a button to see if I would get the same error. The error I received when I tried to press the buttons was:
"Microsoft Visual Basic
400"

I've looked up these errors online and am trying to make sense of them, if you have any input I would certainly appreciate it. I've also attached a portion of the text file for testing.

Thanks!

[JBeaucaire]

Macro seemed to work without error on your sample text file, too.

Here's the macro installed into a basic workbook. Compare this to what you did to see what is different.

[rdlaner]

Thanks.
The workbook you gave me fails (with the same error) at the same point where mine does, when this line is called:
fNAME = Application.GetOpenFilename("Text Files (.txt),.txt")

I am using Excel 2011, if that helps at all.

[JBeaucaire]

Is that a MAC version? I'm not that well-versed in MAC requirements.

[rdlaner]

It is a MAC version, should I expect the functions to be that different...?

[JBeaucaire]

Yes, MAC OS is very different from Windows.

Thanks.
The workbook you gave me fails (with the same error) at the same point where mine does, when this line is called:
fNAME = Application.GetOpenFilename("Text Files (.txt),.txt")

I am using Excel 2011, if that helps at all.

Some Googling suggests something like this might work:

Please Login or Register  to view this content.

I have no way to test that or debug it for you. That "path to documents folder" may be an editable string.

[rdlaner]

Thanks, I'll try out a few things and let you know what I find.

[rdlaner]

I ended up just removing the arguments for the GetOpenFilename function and everything appears to be working now.

Thanks for your patience and help!

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.