Split content with multiple lines into multiple rows and columns in Google sheets

Question

I like to know if it is possible to split an entire paragraph contained in a single cell, into multiple columns and paragraphs using formula's in Google sheets. This is what the text in the cell would look like:

1 coffeeextradark 240gr

1 milkshake 28Oz

5 chocolatebigones 30 pcs

2 andthelistgoeson 34items

And this is how I would like it to be split up into separate cells

1 | coffeeextradark  | 35

1 | milkshake        | 28Oz

5 | chocolatebigones | 30 pcs

2 | andthelistgoeson | 34items

I found these nice examples how this can be done using formulas combined with Excel's Text to columns tool or using VBA, but can someone help me to do the same using Google Apps Script?

Answer 1

Sounds like the =SPLIT function would do what you need. Given that A1 contains the text 1 coffeeextradark 240gr, then

=SPLIT(A1, " ")

^{(notice the space between the quotation marks)}

will split the text into three cells:

1 | coffeeextradark | 240gr