Forum Discussion

fploof's avatar
fploof
Copper Contributor
May 16, 2024

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....
Formulas and Functions
fploof's avatar
fploof
Copper Contributor
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...
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 16, 2024

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

     

     

    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      May 16, 2024
      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!
      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        May 16, 2024

        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.

Resources