I have a data set, that is constantly updated/changing, that I would like to have one column's data separated into multiple rows (and copying adjacent columns to the rows);

I tried to use a script and loop but found I had errors when the records list gets too big. Then I stumbled on this response I found very useful:
Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries)

unfortunately it is a quite complicated formula and some functions are very new to me. Inevitably I had trouble working with it, even with the example from OP.

I have 5 columns where one column will have a variable number of entries. These entries may contain spaces. I have split them in an array, but I do not understand how to transpose and copy so that it makes new rows.


=ArrayFormula(If(not(isblank($A:A)),SPLIT($B:B,", ",,FALSE),))

and I know I need to copy/transpose, for each row:

=len((SUBSTITUTE(B1," ","")))- (len(SUBSTITUTE(B1,"," &" ","")))+1

but I do not know how to use the transpose formula well enough in an array formula to get that.

Where the source has a format of: example raw

and the final output would take this form: example final

  • The title of the question mentions syntax problems but the body of the question doesn't describe them. – Rubén Nov 21 at 2:40
  • I was not familiar enough with the cards in your post (^/ [] etc.) for limiting the response - I also could not get it to work properly on the OPs example - it reiterated row 1 in each entry. so I went back to simplify, but now I would love to understand the transposition you performed in the arrayformula. – rpeak Nov 21 at 2:43
  • I think that you are referring to the second argument of the REGEXEXTRACT function. It's a regular expression. Maybe this guide will help you to start learning the basics : Regular Expressions – Rubén Nov 21 at 2:56
  • Thank you Ruben, I will try that...I am having difficulty with the previous answer though: for each B column (the one being split) it only returns the values from row 1 - although the other columns retain the correct row entry - and I do not know why.... I have tried to take a simplified attack at this and have edited my question to reflect it. Appreciate all feedback and expertise/guidance. – rpeak Nov 21 at 3:10
  • I took a look to the formula but it's not working now, so I will delete temporally my answer until I'm able to fix it. – Rubén Nov 21 at 4:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.