How to quickly transpose an address list from rows to columns in Excel?
Suppose you have a single list containing names and addresses and need to reorganize it into separate columns, as shown below. Excel does not provide a dedicated command for splitting repeating address records into columns, but you can complete the task with VBA or Kutools for Excel.
Transpose an address list with VBA
Transpose an address list with Kutools for Excel
| Method | Best for | Setup | Main consideration |
|---|---|---|---|
| VBA | Users comfortable running and editing macros | Paste and run code that processes each address as a three-row record | The code and column headings must be edited if the record structure changes |
| Kutools for Excel | Quickly rearranging records without writing code | Select Single column to range and enter the number of fields per record | Requires Kutools for Excel |
Transpose an address list with VBA
You can use the following VBA code to transpose an address list from rows to columns.
- Open the worksheet containing the data that you want to transpose, and press Alt + F11 to open the Microsoft Visual Basic for Applications window.
- Click Insert > Module, and paste the following macro into the module:
VBA: Transpose an 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
- Press F5 to run the code. In the first dialog box, select the data range that you want to transpose.
- Click OK. In the next dialog box, select a cell where you want to place the transposed result.
- Click OK. The single list is transposed into columns.
📝 Note:
This macro assumes that every record contains three rows in this order: name, address, and city/state. If your records use a different structure, the VBA code must be adjusted.
Transpose an address list with Kutools for Excel
With Kutools for Excel, you can use the Transform Range feature to transpose an address list into columns without writing or editing VBA code.
- Select the data that you want to transpose, and click Kutools > Range > Transform Range.
- In the Transform Range dialog box, select Single column to range under Transform type. Then select Fixed value and enter the number of fields in each address record. For the example shown here, enter 3.
- Click OK, and then select a cell where you want to place the transposed result.
- Click OK. The address list is transposed into columns.
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
Demo: Transpose an address list with Kutools for Excel
Frequently Asked Questions
What if each address record contains more or fewer than three rows?
With Kutools, enter the actual number of fields in each record as the Fixed value. The supplied VBA code is designed for three-row records and must be edited if your record structure is different.
Does every address record need to contain the same number of rows?
Yes. These methods work best when every record follows the same structure. Missing or extra fields can cause later values to appear in the wrong columns.
Will the transposed result update when the original list changes?
No. Both methods create a separate output range. If the source list changes, run the selected method again to generate an updated result.
Can I place the result in another worksheet?
Yes. When prompted to select the destination cell, switch to the desired worksheet and select the starting cell.
Why are some values appearing in the wrong output columns?
Check that every record contains the same number of source rows and that the fixed value matches that number. For example, use a fixed value of 3 when each record contains a name, an address, and a city/state value.
Best Office Productivity Tools
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in