text to row function

[fploof]

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...

[#2]

@fploof Can you show an example of how it looks like? What Excel version are you using?

[#3]

"Date : 3/18/2024, Amount: 180.00, Description: 2 nights Motel 6 - Repaid 100.00 owes 80
Date : 3/24/2024, Amount: 90.00, Description: Zelle for one night at Motel 6
Date : 3/24/2024, Amount: 90.00, Description: Zelle - 1 night Motel 6 Orangeburg
Date : 4/10/2024, Amount: 50.00, Description: Deposit Budget Inn - Refunded in 3 days
Date : 4/12/2024, Amount: 250.00, Description: 1/2 pay for one week Budget Inn
Date : 4/26/2024, Amount: 73.00, Description: 1 night Arrow
Date : 5/2/2024, Amount: 50.00, Description: Cost Less food voucher
"
***Don't know Excel version, sorry***

[#4]

@fploof 

If those are line breaks in the cell (e.g Ctrl+J), then try this:

=LET(line_break, CHAR(10), TEXTSPLIT(A1, , line_break))

[#5]

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...

[#6]

@fploof 

No need to apologize. Here's a sample workbook with the formula.

[#7]

@Patrick2788 

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λ)

[#8]

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!

[#9]

@Patrick2788 

I have removed the file because it wasn't working correctly.

It is overly complex for any particular problem, but the idea is that for any Lambda helper function that returns the 'nested arrays are not supported' error message from a formula such as

= BYROW(array, Fnλ)

replacing it by 

= BYROWλ(array, Fnλ)

should give the array of arrays.  I copied the function to this workbook to see what happens when each row returns many rows to be stacked.  The outturn is that I have some debugging to do!  My preference would be for Microsoft to extend the Lambda helper functions to return arrays of arrays; that would be far more efficient in several ways!

p.s.  I think the attached is now working correctly!

@fploof 

Please accept my apologies and ignore this.  I was using your problem for testing and formula development purposes.

[#10]

Tx however I discovered a very easy way to do this. I had all data in one colunm which I copied and pasted into word. It did exactly what I needed......tx for your help.

[#11]

@fploof 

I am pleased it worked out for you!  The manual pasting to Word and back (as suggested by @Patrick2788) is something I wouldn't have thought of.  Once I had got over the challenge of "Is it even possible using Excel?", I would most likely have turned to PowerQuery and treated it as an ETL problem, but even that might be regarded as overkill for a one-off task.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitRowsbyLineFeed = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Input data array", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Input data array")
in
    SplitRowsbyLineFeed