Formatting Imported Data

[Junoexpress]

Hi,

I want to be able to read in a single column of data from either an
Excel file or from a column of a word document and then produce the
same column of data so that each entry has two cells separating them.
So that the column

1
2
3

would look like

1
empty cell
empty cell
2
empty cell
empty cell
3

I there a straightforward way I can do this in Excel w/o having to use
some type of additional program (like VBA)?

TIA,
Matt

 

[Gord Dibben]

Nothing straightforward that I can see.

Without some manual arranging which would be onerous, you will be better off to
use a VBA macro.

Sub InsertRows22()
Application.ScreenUpdating = False
Dim NumRows As Integer
Dim R As Long
R = Cells(Rows.Count, "A").End(xlUp).Row
NumRows = 2
For R = R To 1 Step -1
ActiveSheet.Rows(R + 1).Resize(NumRows).Insert
Next R
Application.ScreenUpdating = True
End Sub

FIRST..............make a copy of your original workbook.

Open your copied workbook.

Alt + F11 to open VB Editor. CTRL + r to open Project Explorer.

Select your workbook/project and Insert>Module.

Copy/paste the above macro into that module.

Alt + q to return to Excel.

Save the workbook as a Macro-enabled workbook *.xlsm if running 2007

If running 2003 simply save as *.xls

Alt + F8 and Run the macro

If happy...................glad to assist.

If not happy...............post back.


Gord Dibben MS Excel MVP

 

[Jim Cone]

A non macro approach...
Consecutively number the column adjoining the data.
Copy the numbers and paste them below the first set, do it twice.
Sort both columns by the numbers column.
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/ExtrasXL
(add-in to insert blank rows - with undo option)

..
..
..

"Junoexpress" <[email protected]>
wrote in message
Hi,

I want to be able to read in a single column of data from either an
Excel file or from a column of a word document and then produce the
same column of data so that each entry has two cells separating them.
So that the column

1
2
3

would look like

1
empty cell
empty cell
2
empty cell
empty cell
3

I there a straightforward way I can do this in Excel w/o having to use
some type of additional program (like VBA)?

TIA,
Matt