I'd like to split the data from cell to cell. I have a google sheet and in Column "I" I have technical information about products like this:  

So I'd like to split it as shown in the image. What can I do? or Can you give me directions about which function or formule I need to read to solve this kind of question. Thanks a lot for help

CodePudding user response：

From what I can see, you may need two splits: one to split rows in a cell, and the other to split records in two parts (description, value). To do this we can combine these functions:

• JOIN to get all values in a column as one chunk;
• SPLIT to separate values;
• TRANSPOSE to organise splitted rows vertically;
• ARRAYFORMULA to apply SPLIT as an array function.

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(JOIN(CHAR(10), I:I), CHAR(10)))," "))

Notes:

• JOIN(CHAR(10),I:I) - concatenate the data in the I column into one data block;
• SPLIT(..., CHAR(10)) - split data by Char(10);
• TRANSPOSE(...) - arrange splitted rows vertically;
• SPLIT(..., " ") - split by spaces, note that there's a parameter remove_empty_text which by default is true, i.e. treat consecutive delimiters as one, what suits your case;
• ARRAYFORMULA - make splitting to work as an array-formula.

Example: