I was wondering what the simplest approach would to be transform my data from column 1 and 2 to column 3.
| column 1 | column2 | combined |
|---|---|---|
| monty, alice | [email protected], [email protected] | 1. monty - [email protected] 2. alice - [email protected] |
| matt, bob, ann | [email protected], [email protected] , [email protected] | 1. matthew - [email protected] 2. bob - [email protected] 3. ann - [email protected] |
What I have so far is
="1."&A2&"-"&B2
but this obviously does not allow me to loop through each item, number them and skip lines.
Another idea I had is to unmatch the columns, and apply the formula =&A2&"-"&B2 but then I am lost on how to matched them up in a numbered fashion with line skips.
CodePudding user response：
use:
=ARRAYFORMULA(TRIM(REGEXREPLACE(FLATTEN(QUERY(TRANSPOSE(IF(IFERROR(
SPLIT(A1:A5, ","))="",,SEQUENCE(1, COLUMNS(SPLIT(A1:A5, ",")))&". "&
SPLIT(A1:A5, ",")&" - "&SPLIT(B1:B5, ",")&"×")),,9^9)), "× |×$", CHAR(10))))
