I have raw data with several pieces of information in one cell separated by a special symbol. I would like to be able to rearrange the data into rows but keeping the same ID, name, first name and age.
Please see the image below:
I tried with filters, queries, splits and transpose but I haven't been able to make it work.
The link to my google sheet is: https://docs.google.com/spreadsheets/d/1ECBAJ9jpqmohKAHl-Kpqp1cK5dGW3JiDC38WsWkuzSQ/edit#gid=352138315
try:
=ARRAYFORMULA({SPLIT(TRANSPOSE(SPLIT(CONCATENATE(REPT(
IF(A3:A9<>"", "♥"&A3:A9&"♦"&B3:B9&"♦"&C3:C9&"♦"&D3:D9, ),
LEN(REGEXREPLACE(""&E3:E9, "[^]+", )))), "♥")), "♦"),
TRANSPOSE(SPLIT(TEXTJOIN("", 1, E3:E9), "")),
TRANSPOSE(SPLIT(TEXTJOIN("", 1, F3:F9), "")),
TRANSPOSE(SPLIT(TEXTJOIN("", 1, G3:G9), "")),
TRANSPOSE(SPLIT(TEXTJOIN("", 1, H3:H9), ""))})