Unstacking a Stacked Excel Cell or Parsing Free-form Text to Generate Multiple Rows

markschahl

We all run into some version of this: Excel table formatted to look nice, no thought given to analyzability.

In the free-form text cell, it looks like this:

 

m/d: text [alt+enter]
m/d: text [alt+enter]
m/d: text [alt+enter]
m/d: text [alt+enter]
...
m/d: text [alt+enter]

 

When the data are exported to JMP, the cell gets collapsed into: m/d: text m/d: text m/d: text ... m/d: text.
I want to parse that long string with n elements into a separate row for each element, not split into separate columns. n is variable. This would create a new datatable that copies the other columns and adds the parsed element. Instead of Text-to-Columns, this is Text-to-More-Rows.

Thankfully #/# is used consistently, so I could count those and loop n times.

Has anyone out there done something similar?

pmroz

I do this sort of thing a lot; you just have to slog through it.  Here's an example where Column 1 has multiple values, and Column 2 is static.

dt = New Table( "Test", Add Rows( 3 ), Set Cell Height( 83 ),
	New Column( "Column 1", Character, "Nominal",
		Set Values(
{"5/3: ABCDE
5/4: DEF
5/5: GHI",
"5/6: xyz
5/7: foo
5/8: bar
5/9: Hello
5/10: World",
"4/16: The
4/17: Entire
4/18: Scheme
4/19: Of
4/20: Things
4/21: Is broken"} ),
		Set Display Width( 156 )
	),
	New Column( "Column 2", Character, "Nominal", Set Values( {"A", "B", "C"} ) )
);

dtnew = new table("Text_to_Rows",
	new column("Column 1", Character, "Nominal"),
	new column("Column 2", Character, "Nominal"),
);

delim = "
";
m = 0;
for (i = 1, i <= nrows(dt), i++,
	c1_words = words(dt:column 1[i], delim);
	one_c2   = dt:column 2[i];

	for (k = 1, k <= nitems(c1_words), k++,
		dtnew << add rows(1);
		m++;
		dtnew:Column 1[m] = c1_words[k];
		dtnew:Column 2[m] = one_c2;
	);
);