Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

So i have this text file that looks something like this:

Header1 Header2 Header3 Header4 A1 B1 C1 D1 A2 B2 C2 D2

and so on.

When imported, I'd want the data to format itself in 4 columns. I tried the Get External Data from Text, and it successfully imports it, but it doesn't wrap it around, so it just keeps making columns for every space. I'd want it to go on the next line after 4 (in this case) elements have been added.

What's the simplest way to achieve this?

EDIT: My answer follows, since I'm not yet allowed to answer my own questions yet.

The Excel function I needed is called indirect().

Not sure how it actually works though, so hopefully someone can help out with that, but the function call that worked for me is

=INDIRECT(ADDRESS((ROW(A1)-1)*4+COLUMN(A1),1))

which i found over here: http://www.ozgrid.com/forum/showthread.php?t=101584&p=456031#post456031

Note: this required me to add the text to excel where i'd get this row full of columns, and then flip it so that i'd have a column full of rows.

share|improve this question
    
Hi, welcome to Super User. The simplest way to do it is probably to massage the input file with some other tool and then import it into Excel. What OS are you on? This can be done fairly easily with *nix tools (e.g., sed). By the way, is your input file one absurdly long line, or do you have, say, 400 tokens per line? –  Scott Nov 8 '12 at 0:00
    
Thanks! I'm on Windows. The file is somewhat small so nothing too absurd. –  Alex B Nov 8 '12 at 0:10

1 Answer 1

The Excel function I needed is called indirect().

Not sure how it actually works though, so hopefully someone can help out with that, but the function call that worked for me is

=INDIRECT(ADDRESS((ROW(A1)-1)*4+COLUMN(A1),1))

which i found over here: http://www.ozgrid.com/forum/showthread.php?t=101584&p=456031#post456031

Note: this required me to add the text to excel where i'd get this row full of columns, and then flip it so that i'd have a column full of rows. Function could probably be modified to work with my original setup, but I didn't have time for that at the time. Will probably revisit it soon.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.