How to quickly transpose address list from rows to columns in Excel?
For example, there is a single list combined with names and addresses, and you need to convert the list to columns as below screenshot shown. There is no built-in function in Excel to handle it, but, in this article, I can introduce some tricks on solving this quickly.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
To transpose address list from rows to columns, you can apply a VBA code.
1. Enable the sheet containing 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
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:
4. Click OK, then in the next popping dialog, select a cell to place the transposed result. See screenshot:
5. Click OK. Now the single list has been transposed into columns.
If you have Kutools for Excel, you can apply the Transform Range feature to quickly transpose address list to columns.
|Kutools for Excel, with more than 120 handy functions, makes your jobs easier.|
After installing Kutools for Excel, please do as below: （Free Download Kutools for Excel Now!)
1. Select the data you want to transpose, click Kutools > Range > Transform Range. See screenshot:
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:
3. Click Ok, and select a cell to output the result. See screenshot:
4. Click OK. The address list has been transposed.