Google Sheets formula to split a cell that contains a null value - Stack Overflow

admin管理员组

文章数量:1292117

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

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

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:

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

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

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/spreadsheets/d/1r5wKNfnvC7llG8OlfkAiVmE3qy0MOo6DFoDOBMpt-Jo/edit?usp=sharing

• google-sheets
• google-sheets-formula

Share Follow asked Feb 13 at 10:01 AnnaAnna 7111 silver badge77 bronze badges 1

• Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. – TheMaster Commented Feb 14 at 1:46

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

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

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));Λ)))))))

本文标签： Google Sheets formula to split a cell that contains a null valueStack Overflow