Convert multi column data in a single row that contains delimiter, to a multi row and flatten delimiter so only one item is in each cell

Question

I am looking to split, and flatten, with an array formula, and keep the correct corresponding Reference Number, assigned to multiple rows of flattened data.

• Column A: Reference Number
• Column B: ID (CSV)
• Column C: TEMP ID (CSV)

Current Format

| A |        B      |       C      |
|111|001 002        |              |
|222|004 005 006 008|              |
|333|007            |T001 T006 T002|
|888|               |T005 T004 T008|
|444|               |T007          |

Expected Results

| A | B | C  |
|111|001|    |
|111|006|    |
|222|004|    |
|222|005|    |
|222|006|    |
|222|008|    |
|333|007|    |
|333|   |T001|
|333|   |T006|
|333|   |T002|
|888|   |T005|
|888|   |T004|
|888|   |T008|
|444|   |T007|

Here is another post which is similar to what I am looking to accomplish. (The only difference is in this post, the OP only had 2 columns.)

Here is the formula that I'm using (Not working perfectly):

=ARRAYFORMULA(TRIM(QUERY(SPLIT(FLATTEN(IF(IFERROR(SPLIT('Form Responses'!C2:D, " "))="",,
'Form Responses'!B2:B&"×"&SPLIT('Form Responses'!C2:D, " "))), "×"), "where Col2 is not null")))

What am I doing wrong?

Here is my sheet.

Answer 1

Use this:

=arrayformula( 
  regexreplace( 
    text( 
      split( 
        query( 
          flatten( iferror( 'Form Responses'!B2:B & "µ" & iferror(split('Form Responses'!C2:C, " ")) & "µ" & iferror(split('Form Responses'!D2:D, " ")) ) ), 
          "where Col1 is not null and not Col1 ends with 'µµ' ", 
          0 
        ), 
        "µ", false, false 
      ), 
      "000" 
    ), 
    "000", "" 
  ) 
)

Note that this formula will produce a row that looks like this:

Reference	#ID	Temp ID
333	007	T001

I.e., source data rows that have both an ID and a Temp ID will share a reference number. To make these IDs go to their own rows, use this:

=arrayformula( 
  regexreplace( 
    text( 
      split( 
        { 
          query( 
            flatten( iferror( 'Form Responses'!B2:B & "µ" & iferror(split('Form Responses'!C2:C, " ")) & "µ" ) ), 
            "where Col1 is not null and not Col1 ends with 'µµ' ", 
            0 
          ); 
          query( 
            flatten( iferror( 'Form Responses'!B2:B & "µµ" & iferror(split('Form Responses'!D2:D, " ")) ) ), 
            "where Col1 is not null and not Col1 ends with 'µ' ", 
            0 
          ) 
        }, 
        "µ", false, false 
      ), 
      "000" 
    ), 
    "000", "" 
  ) 
)

The result table will have all the ID rows first, followed by the Temp ID rows.