Google Sheets- Split Cell w/ Data and Organize?

Question

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.

Answer 1

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

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

Answer 2

Added solution to your sheet here:

=makearray(rows(A2:A),4,lambda(r,c,ifna(regexextract(index(A2:A,r),index(to_text(B1:E1),,c)&" \| (.*?)"&CHAR(10)))))