convert tables from word to excel and transpose them 1

[gupy]

Hi, Ive got this problem:

word doc with text in tables like:
---
a (name1)

b (address1)

c (phone 1)
---
a (name2)

b (address2)

c (phone2)
--- and so on...

note that a+b+c are in the same cell and separated with enter(s)

I need to import them into excel and they shoul look like:
a name1 \ b address1 \ c phone1
----
a name2 \ b address2 \ c phone2

(each a in its own cell, each b in its own cell, and so on...)

How to export from Word, import into Excel? Ive got around 200 pages of such tables in Word so I would really be happy if anyone knows the solution.

Copy/Paste and Paste Speciale do not work (at least not for me). With coy/paste word-excel there are 1. empty cells and rows because of ABCs in word separated with enter and 2. Paste speciale seems to paste only first ABC corectly, theb everything is screwed up....

Thx in advance,

Gupy

 

[snowchild]

Since your text is in a table, you can go to your Word file, highlight the whole file, then click on Table, Convert, Table to Text, then save the file as a *.txt file (text only with line breaks), click on yes when it prompts you to save. Then go to Excel and pull up the text file and the import wizard will start up. Select Delimited, then click on Next, then uncheck the Tab box and check the Comma box, then click on finish and it should be okay but you will then need to use the TRIM function in Excel to take out the extra spaces at the beginning of each field.

Hope this helps!

 

[gupy]

Snowchild,

thx for reply... it works for a and b, but c is still where it sould not be. In Excel it looks like:

a b
c

but should be a b c

Why so?

thx

gupy

 

[gupy]

Hm... it goes like this...
word saves from table to text like:
a
b
c

in my tekst:
a= text1, text2
b= text3
c= text4

in excel then (after your suggestion) appears like:
a=text1 / a=text2
b=text3
c=text4

(note that "/" = end of cell)

but i need
a=text1,2 / b=text3 / c=text4
and so on...


Gupy

 

[snowchild]

Gupy,

It sounds like there is a carriage return in the "C" field. Try this then, Select the entire table in Word and copy it, then go to Excel and paste it. It should paste it exactly the same way that Word sees it into Excel. If there is a carriage return in the field, you will see it and if there isn't you should be good to go. Let me know!

Snowchild

 

[gupy]

I belive this problem must be solved with transpose function in Excel...

Anyone know how to make a right input?

 

[gupy]

Snowchild...

hmhm... I did copy/paste... and .. everything is messed up.

would you be so kind and mail me at gupy@siol.net... perhaps i can send you a page or two from my file and you can look over it... if you have time.

Thx,

Gupy

 

[xlhelp]

In Word, hightlight the whole table (or all the data. Go to Edit, Replace.

In the find box type ^p and in the replace box type , (comma) and then replace all.

Select all the data, copy; switch to Excel, Paste.

With the pasted cells still highlighted, go to Data, Text to columns, Choose Delimited, in the next step check the box for Comma, click Next, Click finish

 

[gupy]

xlhelp, not working.. because Ive got 850 of a, b, and c.
then in Excel everything goes like a1b1c1 a2b2c2 a3b3c3 until a850b850c850.

But here you go wih my solution:

Word: Put all data into one cell (its cool if you do it via Excel, so you can later count your inputs and divide them), split this cell (back in Word) to x-times (x=850/3 (ive got to put 3 lines - abc into one)) into rows, then paste to Excel and transpose them.

Thats it.

Anyway thx for help, Snowchild and xlhelp.

Gupy