Skip to main content

How to quickly transpose address list from rows to columns in Excel?

Author: Sun Last Modified: 2024-11-18

For example, you have a single list containing names and addresses, and you need to convert it into columns, as shown in the screenshot below. Excel does not have a built-in function for this, but this article introduces some quick methods to solve it.
A screenshot showing an address list transposed from rows to columns in Excel

Transpose address list by VBA

Transpose address list by Kutools for Excelgood idea3


Transpose address list by VBA

To transpose address list from rows to columns, you can apply a VBA code.

1. Open the sheet containing the data you want to transpose, and press Alt + F11 keys to open Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste below macro code to the Module. See screenshot:

VBA: Transpose address list

Sub fixText()
'UpdatebyExtendoffice0170905
    Dim I As Integer
    Dim K As Integer
    Dim xRgS As Range
    Dim xRgD As Range
    Dim xAddress As String
    On Error Resume Next
    xAddress = ActiveWindow.RangeSelection.Address
    Set xRgS = Application.InputBox("Select a range to transpose:", "KuTools For Excel", xAddress, , , , , 8)
    If xRgS Is Nothing Then Exit Sub
    Set xRgD = Application.InputBox("Select a cell to place result", "KuTools For Excel", , , , , , 8)
    If xRgD Is Nothing Then Exit Sub
    xRgD(1).Offset(, 0).Value = "Name"
    xRgD(1).Offset(, 1).Value = "Address"
    xRgD(1).Offset(, 2).Value = "City/State"
    K = 1
    For I = 1 To xRgS.Rows.Count
        xRgD(1).Offset(K).Value = xRgS(I).Value
        xRgD(1).Offset(K, 1).Value = xRgS(I + 1).Value
        xRgD(1).Offset(K, 2).Value = xRgS(I + 2).Value
        K = K + 1
        I = I + 2
    Next
End Sub

A screenshot of the VBA editor with the pasted macro code

3. Press F5 key to run the code, and a dialog pops out to remind you select the data range you want to use. See screenshot:
A screenshot of the dialog for selecting the range to transpose in Excel

4. Click OK, then in the next popping dialog, select a cell to place the transposed result. See screenshot:
A screenshot of the dialog for selecting a cell to place the transposed result

5. Click OK. Now the single list has been transposed into columns.
A screenshot showing an address list transposed from rows to columns


Transpose address list by Kutools for Excel

If you have Kutools for Excel, you can apply the Transform Range feature to quickly transpose address list to columns.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

1. Select the data you want to transpose, click Kutools > Range > Transform Range. See screenshot:
A screenshot showing the Transform Range option in Kutools tab in Excel

2. In the popping out dialog, check Single column to range option in Transform type section, and check Fixed value option and type the number you need in the right textbox. See screenshot:
A screenshot of the Transform Range dialog with options to transpose data from a single column to a range

3. Click Ok, and choose a cell where you want to place the transposed result. See screenshot:
A screenshot of the Kutools dialog for selecting a cell to place the transposed result

4. Click OK. The address list has been transposed.
A screenshot showing an address list transposed from rows to columns

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now

Demo: Transpose address list by Kutools for Excel

Kutools for Excel: Over 300 handy tools at your fingertips! Enjoy permanently free AI features! Download Now!

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!