Search code examples
google-sheetsgoogle-sheets-formulaspreadsheetspread-syntaxspreadsheetgear

How to combine data and then separate concatenated data?

I would like to combine a range of data into one single cell G5.

Then I want to split Value G5 into original form. Thank you so much.

https://docs.google.com/spreadsheets/d/1sVrpPvtQPG0Zw2Ar_UxPHXNN0atEdQNX-hkyB2ixUGw/edit?usp=sharing

enter image description here

enter image description here

Solution

  • ER1:

    =ARRAYFORMULA(TRIM(REGEXREPLACE(QUERY(FLATTEN(QUERY(TRANSPOSE(
 {A1:B3&" |", C1:C3}),,9^9))&"♦",,9^9), "♦ |♦$", CHAR(10))))

    enter image description here

    ER2:

    =ARRAYFORMULA(TRIM(SPLIT(FLATTEN(SPLIT(G5, CHAR(10))), "|")))

    enter image description here