Transpose an Address List as Row in Google Spreadsheet

Question

I'm having a good old list with addresses:

Because I want to re-arrange the list (and make it more 2013), I need to transpose the list like this:

I've tried to use the TRANSPOSE formula, but that doesn't work. Is there some other way?

Answer 1

If the data has the same repetition as shown in your example, throughout the list, then the following formula would help you out:

=TRANSPOSE(INDIRECT("A"&(1+(5*(ROW(B1)-1))&":A"&(4+(5*(ROW(B1)-1))))))

Copy the formula downwards until it hits a ref#.

I've included a Google Spreadsheet file for you to take a look: Transpose an Address List

Good luck with your 2013-ish address list.