Forum Discussion
text to row function
I have data that is exported from an application that puts mutiple rows of data in a single cell. I need to extact those embedded rows from cell and put into their own rows!! Show me the way please....
If those are line breaks in the cell (e.g Ctrl+J), then try this:
=LET(line_break, CHAR(10), TEXTSPLIT(A1, , line_break))
Sorry, but I have no idea where to insert this. I have 1 colunm with 478 rows and about 100 cells have these types of entries...
No need to apologize. Here's a sample workbook with the formula.
The fun starts when one has an entire array of such lists!
MAP really does need considerable improvement to deal with arrays of arrays.
Taking a single cell formula further
= LET( line_break, CHAR(10), sep, {",", ":"}, list, TEXTSPLIT(target, sep, line_break), values, TAKE(WRAPROWS(TOCOL(list), 2), , -1), header, {"Date", "Amount", "Description"}, VSTACK(header, WRAPROWS(values, 3)) )The attached makes a start at using bisection to build an array of arrays but
1. I have used BYROW because I have yet to implement MAP
2. The helper function was not written for more than one row returned from a given step of BYROW.
I think it is all a bit fragile!
= BYROWλ(targetList, SPLITBOOKINGλ)- I've been accused of authoring "overly complex" formulas so I'd be inclined to copy/paste the text to Word (unformatted text) and then back to Excel and call it a day!
