CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Anna
Anna

Reputation: 71

Google Sheets formula to split a cell that contains a null value

I have data related to Product with the following data example: Data

I want to clean it up into the expected output as follows: Expected Output

For the output, I have tried the formula:

=LET(
  item_code; FLATTEN(A4:A9 & B4:B9 & C4:C9);
  item_name; FLATTEN(D4:D9);
  production_code; FLATTEN(E4:E9);
  
  test_type_1; SPLIT(FLATTEN(F4:F9); CHAR(10));
  test_req_1; SPLIT(FLATTEN(G4:G9); CHAR(10));
  test_type_2; SPLIT(FLATTEN(H4:H9); CHAR(10));
  test_req_2; SPLIT(FLATTEN(I4:I9); CHAR(10));
  
  FILTER(
    HSTACK(item_code; item_name; production_code; test_type_1; test_req_1; test_type_2; test_req_2);
    test_type_1 <> ""
  )
)

but the formula used still produces wrong output and errors. I want to separate each stage from test type and test requirement. Any suggestions for improving the formula?

Here's the test sheet link: https://docs.google.com/spreadsheets/d/1r5wKNfnvC7llG8OlfkAiVmE3qy0MOo6DFoDOBMpt-Jo/edit?usp=sharing

Upvotes: 0

Views: 72

Answers (2)

Babanana
Babanana

Reputation: 1476

You may also try this approach

Utilizing the Split to Separate the Values and Scan and Regexreplace for removing the "#. " Before each value.

=LET(x; WRAPROWS(TOROW(BYROW(A4:I9; LAMBDA(k; TOROW(LET(x;CHOOSECOLS(k;1;2;3);t;CHOOSECOLS(k;4;5);y;CHOOSECOLS(k;6;7;8;9); z; BYCOL(y; LAMBDA(z; IF(ISBLANK(z);" ";TOCOL(SPLIT(z;CHAR(10))))));q; SCAN("";z; LAMBDA(a;c; JOIN(a;REGEXREPLACE(c ; "^\d+\.\s*"; ""))));BYROW(q; LAMBDA(m; HSTACK(JOIN("";x);t;m))))))));7); FILTER(x; CHOOSECOLS(x;1) <> ""))

Result

Production Code Test Type (1) Test Requirement (1) Test Type (2) Test Requirement (2)
A10001 Pencil 1 P1 Physical Strength Testing Breakage resistance
A10001 Pencil 1 P1 Writing Performance Testing Smoothness, Darkness
A10001 Pencil 1 P1 Heat & Moisture resistance
A10001 Pencil 1 P1 Ergonomic Testing
A10002 Pen P2 Ink Quality Testing Smudge resistance
A10002 Pen P2 Writing Performance Testing
A10002 Pen P2 Ink Longevity Testing Drying time, Shelf life
A20101 Book P3 Printing Quality Testing Page alignment, Ink fade
A20101 Book P3 Paper Durability Testing Tear & Water resistance
A20101 Book P3 Binding Strength Testing Page detachment resistance

Upvotes: 2

rockinfreakshow
rockinfreakshow

Reputation: 30240

You may try:

=arrayformula(reduce(tocol(;1);sequence(match(;0/(A4:A<>"")));lambda(a;c;iferror(vstack(a;let(Λ;bycol(index(F4:I;c);lambda(Σ;mid(tocol(split(Σ;char(10)));4;9^9)));
        hstack(chooserows({join(;index(A4:C;c))\index(D4:E;c)};sequence(rows(Λ);1;1;0));Λ)))))))

enter image description here

Upvotes: 1

Related Questions