Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am doing a directory for my neighborhood. We had most of the information from a previous directory. The information was entered: A1 name, B1 address and C1 phone number; B1 name, B2 address, C2 phone number etc. The publisher wants the information in a different format A1 name, A2 address, A3 phone number, A4 blank; A5 name, A6 address, A7 phone number, A8 blank etc... Is there an easy (or heck - a not so easy) way to have Excel change the format of the information without me having to hand type 1300 households information?

I will also need to reformat the information a second time into a crisscross. The format for that one is: A1 Street name, A2 Address Number, B2 Resident Name and C2 Phone number.

share|improve this question

migrated from Dec 3 '09 at 17:19

This question came from our site for professional and enthusiast programmers.

While there are programming-related answers to this question, the most obvious one involves copy-paste and is not programming-related. – Pascal Cuoq Dec 3 '09 at 17:13
Just to clarify what you want. Each record is entered horizontally in a row, and you want to convert them to vertical records with a space between records? If so, that can be done with a macro pretty simply. – datatoo Dec 3 '09 at 18:17
The title of this should be edited to better reflect the question. – kzh May 24 '10 at 20:45

Excel has a feature to do that e.g. move from A1 A2 A3 to A1 B1 C1. Select the data in question, copy it and then go to the area where you want to past it. Then do not simply paste, but past "past special" (or paste content or whatever it says, in German it says "inhalte einfügen" ;-)). The upcoming dialog box has a transpose check boy that will help you achieve your solution.

share|improve this answer
Yes +1 and you can incorporate this into a macro and do the whole sheet pretty easily. if that is what you are trying to do. – datatoo Dec 3 '09 at 18:19
Transpose won't get the single-column arrangement she needs - it will simply flip the data 90 degrees. – Martha Dec 3 '09 at 19:51

Add this macro to your excel, and run 'MyTranspose' when the sheet containing your address data is selected.

Option Explicit

'Copy all the data from the active sheet into a  new worksheet
' and transpose all the columns into a single column
Sub MyTranspose()
    Dim r As Range
    Dim wsNew As Worksheet
    Dim wsCurrent As Worksheet

    Set wsCurrent = ActiveSheet
    Set wsNew = Worksheets.Add(Before:=Sheets(1))

    For Each r In wsCurrent.UsedRange.Rows
        wsNew.Cells(LastRowOnWS(wsNew, 1) + 2, 1).PasteSpecial Transpose:=True
    Next r

    Application.CutCopyMode = False
    Set wsCurrent = Nothing
    Set wsNew = Nothing
End Sub

'Find the last row used in column 'col'
Function LastRowOnWS(ByVal ws As Worksheet, _
                     Optional ByVal col As Long = 1) As Long
    LastRowOnWS = ws.Cells(65536, col).End(xlUp).Row
End Function
share|improve this answer

The first one - converting a data table to a single column with blank rows separating each record - I'd actually do in Word. Copy & paste the data into Word, convert table to text using Tab as the column separator, replace each existing paragraph mark with two paragraph marks (^p with ^p^p), replace each tab with a paragraph mark (^t with ^p), and copy and paste the result back into Excel.

For the second task, if the addresses are formatted the usual US way - 123 Main Street - then you'll need to use Excel's text manipulation functions to split out the address number from the street name. If the addresses are in column B (and there is always a single space separating the number from the street), then in D1, put

=Trim(Left(B1,Find(" ",B2)))

and in E1, put


and fill down. Then, as before, use Word to strategically replace tabs with paragraph marks to get the arrangement you need.

If it's stupid but it works, it's not stupid. :)

share|improve this answer
Unusual, but seems to work! – DaveParillo Dec 3 '09 at 20:36

Manipulation of the excel concatenation tool should help

share|improve this answer

This question better belongs on, but since you've asked in SO, which is a programming discussion site...

I'd definitely do the following:

  1. Export the data into a .csv file
  2. Manipulate the .csv file with a powerful programming language like Python
  3. Import the new .csv file back to excel

Learning such tools can be advantageous to everyone, not just programmers!

share|improve this answer

You must log in to answer this question.