Help - Moving data from across multiple columns into rows

I am looking for help on how to move data for every 6 columns into a consecutive row: (all data is currently in Row 1 only, across thousands of columns!)

For example

Column: A B C D E F G H I J K L
Row 1: Joe Blow 123 Easy Street San Jose CA 95124 (408) 123-4567 Mary Sue 456 Glover Street Cupertino CA 95014 (408) 253-1234

I would like for this to convert to:

Column: A B C D E F
Row 1: Joe Blow 123 Easy Street San Jose CA 95124 (408) 123-4567
Row 2: Mary Sue 456 Glover Street Cupertino CA 95014 (408) 253-1234

Please help!

Originally Posted by leelnich

Hi cadler1, welcome to the forum! Paste this formula in A2, then copy across to F2. Now select all 6 of THOSE cells and copy down.:

Please Login or Register  to view this content.

I must not be doing something correctly. Because this is not working for me. Can you provide a little more detail?

Originally Posted by cadler1

I must not be doing something correctly. Because this is not working for me. Can you provide a little more detail?

Nevermind! Thank you! It worked, I messed something up the first time.

Originally Posted by cadler1

Nevermind! Thank you! It worked, I messed something up the first time.

What about rather than having the data separate every 6th row, would it be possible separate the data by by specific word in column? For example after every separate address in Row 1 is the word "map". Would it be possible to separate the addresses using that word? I ask that because not all addresses are separated by 6 columns. Most are, but some are missing fields. But all have the word "map" in the column after them.

Originally Posted by leelnich

Barring an upload, at least post an example string showing "map" in context. Is it in the first cell of each "section", or the final cell, or what? Is it the first word in the cell, or somewhere in the middle?

Thank for your help so far. Actually, I was able to delete two columns in row 1 (where all the data is) that I don't need. Now I just need to apply your original offset code to move data for every 4 columns now in a consecutive row. However, no your off set code is not working. Even when I change it from "=OFFSET(A$1,,(ROWS(A$2:A2)-1)*6)" to =OFFSET(A$1,,(ROWS(A$2:A2)-1)*4). I paste that code in A2 and drag across to D2. Then I copy those fields and drag them down. However, all it does is repeat the same content of A2 all the way down. What am I missing?