Search code examples
datetimegoogle-sheetsformula

Google Sheets- Split Cell w/ Data and Organize?

Hello Sheets Experts,

I'm looking to analzye some time data that I received in a Google Sheet. Unfortunately, the original data is in a less than friendly format.

Sample Sheet w/ Data: https://docs.google.com/spreadsheets/d/1-eyyVs67pp4nyL7jboWmOkGnrkNuuwiPP2RVv_SuDz0/edit?usp=sharing

Ideally, I would like to take this "source data" (around 1500 separate cells) Source Data

and pull the time listed for the last 4 days (indicated in my headers) and organize into separate cells for further analysis (like shown below, which I did manually): Ideal Result

The tough thing is that each cell is unique as it contains a variety in both the quantity and calendar date.

Is there a way to break down the data in the column A to achieve my desired result?

I tried splitting the text to columns, which I can do 1 by 1- but am hoping there is a "smarter" way to do this with 1500 rows of data split column

Ablebits powertools seems like it may help, but I don't have a subscription and am looking for a "free" way to do this via a formula.

Solution

  • You can try with this formula:

    =INDEX(VLOOKUP(B1:E1,INDEX(IFERROR((SPLIT(FLATTEN(SPLIT(A2:A13,CHAR(10)))," | ")))),2,0)&" min.")

    It splits the values by the line separator (CHAR(10)), makes it a column with FLATTEN, splits again by "|" and FLATTEN; and then do a VLOOKUP with the headers

    enter image description here

    To the full range you can use:

    =BYROW(A2:A,LAMBDA(a,IF(a="",{"","","",""},INDEX(IFNA(VLOOKUP(B1:E1,INDEX(IFERROR((SPLIT(FLATTEN(SPLIT(a,CHAR(10)))," | ")))),2,0)&" min.")))))