Word to Excel Conversion

[fbacchus]

Hi All:

I have a Word table which I would like to convert to an Excel Spreadsheet and maintain the same formats. Any suggestions ?

Thanks in advance.

fb Frank M. Bacchus, apc
Informations and Telecommunications Leader

 

[JustImagine2525]

Frank

What version of Word and Excel are you running? Have you tried a staight highlight, copy and paste into Excel? If so, what did not happen correctly? What type of formats are you wanting to preserve? What type of fonts are you using?

justimagine2525

 

[fbacchus]

Justimagine2525,

The table is in Word 2002 under XP. I've tried to copy directly byt it isn't coming out correctly (or the what I was hoping it will) For example, in the first column of the table I have a fill Name, address,city,state zip coutry, all on seperate lines in that same cell (issuing a return at the end of the line to get the next line). When I paste this into excel each line is going into a seperate row (I guess excel is interpreting the "enter(carriage return)" as an indication to put the field in a seperate row. There are no fancy fonts just bolding. The columns across the Word table contains other information but the same happens. I wanted each row in the word table to be equivalent to cells A1, B1, C1 D1 etc. Does this help ?

frank
Frank M. Bacchus, apc
Informations and Telecommunications Leader

 

[JustImagine2525]

Frank

Copy and paste the table from word to excel. Select the entire worksheet and go to format, cell, wrap text. Then paste. Are you pasting the table to cell A1, start of worksheet? If so first thing you need to do is go to the last cell of data below the row of your data. for example if you paste your name, add, city, st and zip into cell a1 you should have name in a1, add in cell a2, city in cella3, st in cell a4 and (if you have a break for the zip) zip in cell a5. So go to, say, cell a6 and paste the following code/function

=A1&CHAR(10)&A2&CHAR(10)&A3&CHAR(10)&A4&CHAR(10)&A5

if the info is in different cells then adjust the a1, a2, a3 etc...

Now if you have a couple of these you can just drag the formula in cell a6 over to the right until you get all addresses combined. If there are a lot then I would set up a worksheet template. Therefore everytime you paste the results automatically occurr. For example. Lets say you have a three row table by 4 columns so you want to have a 3 x 4 table in excel. Lets say you have 5 datums for each MS WORD row x column table (name, add, city, state, Zip) you will end up with an excel spreadsheet having 15 rows by 4 columns. Unacceptable!?! So if you take the formula above and paste it into the last populated cell of your sheet you will then get your first row pasted into an excel cell. For the row number two in the word table you will need to adjust the formula by the number of data. For example for the address you would write

=A6&CHAR(10)&A7&CHAR(10)&A8&CHAR(10)&A9&CHAR(10)&A10

since we added 5 data then we add 5 to the formula row reference.

okay, you can autofill this down by selkecting the two cells with these two formula and dragginging to your hearts content.
If you have different number of data in the other WORD columns, then adjust the formula above to only capture the number of dsta.

I did not offer a programming solution because I do not know your level of expertise and it becomes a mainteneace issue down the road. Also, you could make this real sweet by doing all of this in a separate sheet and referencing the final product to the pasted cells, say, in sheet one.

By The Way BTW a hard carriage return in Excel is ALT ENTER. char(10) is the ansi code for alt enter

Also, hope this helps, pose more questions if necessary I will check back.

Mark