I have a question about combining rows to a column in Google Spreadsheets. There are a lot of threads already about this topic, but I couldn't find one about this particular problem.

This is the data I have at the moment.

  • Name1 Name_1_belonging_to_1 Name_2_belonging_to_Name_1
  • Name2 Name_1_belonging_to_2 Name_2_belonging_to_Name_2
  • Name3 Name_1_belonging_to_3 Name_2_belonging_to_Name_3

What I need is:

  • 1 Name1
  • 2 Name_1_belonging_to_1
  • 3 Name_2_belonging_to_Name_1
  • 4 Name2
  • 5 Name_1_belonging_to_2
  • 6 Name_2_belonging_to_Name_2
  • 7 Name3
  • 8 Name_1_belonging_to_3
  • 9 Name_2_belonging_to_Name_3

You can do this using split, join and transpose and just adjust your range to include the actual number of rows:

=transpose(split(join(" ",A2:A3)," "))

enter image description here


Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.