Thursday, March 21, 2013

How to Convert Multiple Columns in Excel to a Single List in Word


1. Open the Excel 2010 file that you want to work with. Right-click on the 'A' above the first column and choose 'Insert.' This creates a blank first column that you will use to construct your list.
2. Press 'Alt' and 'F11' to launch the Excel VBA console. Right-click on any worksheet in your current workbook -- these are listed on the left side of the console -- move your mouse over 'Insert' and choose 'Module.' Double-click on the module which appears in the list.
3. Copy the following code and paste it into the white space on the right side of the VBA console:Sub Combine()Range('B1').SelectDo While ActiveCell > ''Range(ActiveCell, ActiveCell.End(xlDown)).Copy Destination:=Range('A10000').End(xlUp).Offset(1, 0)ActiveCell.Offset(0, 1).SelectLoopEnd SubThis code creates a macro, called 'Combine,' which combines all adjacent columns, starting with column 'B,' into one long list in column 'A.' If you think the total number of cells will be larger than 10,000, increase the number '10000' in the code so that it will be larger than the number of all your cells combined. The macro runs until it encounters a blank cell in the top row of a column.
4. Click the 'Play' button in the middle of the bar at the top of the VBA console. This creates your list in column 'A.' Click the 'X' in the top-right corner of the VBA console to close it.
5. Select the 'A' above the first column to select the entire column. Press 'Ctrl' and 'C' to copy the information to your clipboard.
6. Open the Microsoft Word 2010 file where you want to paste the list. Click the document to place your cursor wherever you want to insert the list.
7. Click the bottom of the 'Paste' button to open up a pop-up window. Choose the icon labeled with a large 'A' to insert the information as text.
8. Click the last item in the inserted items and hold the mouse button down. Drag the mouse up to the first item and release the mouse button, selecting the entire range of items. Click the 'Home' tab at the top of the screen and find the 'Paragraph' section. Click the 'Bullets' or 'Numbering' buttons to turn the information into a list.

Blogger news