Import data in Excel that doesn't have a row delimiter, but number of columns is known
- by Alex B
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.
