I have data in Google Sheets that looks like

    A        B
1 Name:  John Smith
2 Email: john@smith.com
3 Name:  Dan Jones
4 Email: dan@jones.com
... many rows

which I'd like to get into this format:

   A            B
1 Name        Email
2 John Smith  john@smith.com
3 Dan Jones   dan@jones.com

This is different from just a straight transpose because we're dealing with repeating sets of rows that need to be repeatedly transposed into columns.

What's the best, fastest way to do this?


You can use the following query formula:

={{"Name";QUERY({B1:B},"where Col1 is not null and  not Col1 contains '@'")},
  {"Email";QUERY({B1:B},"where Col1 contains '@'")}}

enter image description here

Functions used:

  • Welcome. Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. Jun 14 '20 at 19:46
  • Thanks. Just going with this answer as it is the cleanest solution so far.
    – Daniel May
    Jun 14 '20 at 21:51

In C1 enter:


and copy downward. In D1 enter:


and copy downward:

enter image description here

If you need to enter the formulas below row#1, replace:



  • Thanks for this. I'm just going for the voted answer because it's just a bit cleaner with not having to copy downward the formula.
    – Daniel May
    Jun 14 '20 at 21:51
  • @DanielMay I agree with you completely! The marikamitsos post is really cool. I am going to up-vote it myself. Jun 14 '20 at 22:52

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.