Impossible! - Coply multiple lines into single cell

[vxooxv]

I have a table in word that I'm trying to copy into excel.

The problem is one of the columns in the row in that table may have multiple lines with hard returns.

When I copy and paste into excel, the result gives me multiple lines. I want a single line, with the exact multiple text in a single cell. How can I do this?? Nobody in the world seems to know.

To try this set up a word table with a single row, 3 columns. In the 3rd column put the following value.

Value 1
Value 2
Value 3

Now copy the entire row and attempt to past into excel. Watch as it creates 3 rows.



Thanks in advance!

 

[Tazguy37]

I copied the entire table and used "Paste Special" as text, and I got 1 row.... Everything in the 3rd column (from your example) pasted into a singe cell.

 

[vxooxv]

What version of Excel are you using. I'm using 02

Let me run this through you again to make sure we are both on the same page.

Create a web table in word. 1 row, 3 columns.
A1 value = A
B1 value = B
C1 value =
C<hard return>
D<hard return>
E

Select the entire table in word.

Paste regular in excel cell A1 I get.:

A1 value = A
B1 value = B
C1 value = C
C2 value = D
C3 value = E

Paste special text in excel cell A1 I get:

A1 value = A
B1 value = B
C1 value = C
A2 value = D
A3 value = E

What am I doing that you aren't??

 

[Tazguy37]

Book1
	A	B	C	D
1	A	B	CDE
2
Sheet1

A1: A
B1: B
C1: CDE

 

[vxooxv]

After further look.

That isn't the result.
The cell should appear as

A
B
C

Not
ABC

As I stated earlier, in the A3 cell in word, type A<press enter> type B<press enter> type C

The result will look like
A
B
C
Not ABC..

When you paste that in excel however,
You get
A
B
C

However A,B,C are in different cells, I need them to remain in verticle, in the same cell.

 

[Ekim]

In Word
1. Select the table.
2. Go to the Edit menu | Replace.
3. In Find What, type “^p” (no quotes – that’s a “^” followed by the letter “p”. The “^” is on the same key as the number 6.
4. In Replace With, type something unique i.e.that won't be in the table, e.g. “&xxx&” (no quotes).
5. Click Replace All.
6. Click Close.
7. Select the Table, right click, and select Copy.

Note: “^p” is a Word paragraph mark, which are not recognized by Excel. We are simply replacing them with “&xxx&”.

Switch to Excel
1. Select a cell, right click, and select paste.
2. Go to the Edit menu | Replace.
3. In Find What, type the string used above i.e. “&xxx&” (no quotes).
4. In Replace With, hold down the Alt key, and on the number keypad (to the right of the regular keys) type 0010 (nothing will show in the Replace box). In the Replace dialog box, make sure to uncheck “Match entire cell contents”.
5. Click Replace All, OK, Close.

Note: Alt-0010 is the worksheet function that creates a line feed in Excel.

The above can be semi-automated via macros.

HTH

Mike

 

[vxooxv]

Followed your instructions to a T.

Doesn't seem to work for me. Office xp, Excel Version 2002 SP3

When I do the replace, it seems to clear out ALL cells, even if it has the &xxx& in it or not.

Any clue as to what might be causing that?

 

[martinee]

That was neato Ekim, it worked for me, and I have the same versions as th OP. I don't know why it doesn't work for him.

 

[Ekim]

Vxooxv,

I’m using Excel XP as well so it’s not a version issue (and Martinee reports that the routine works for him).

Step 4 of the above instructions (under Excel) trips up many people:

In Replace With, hold down the Alt key, and on the number keypad (to the right of the regular keys) type 0010 (nothing will show in the Replace box). In the Replace dialog box, make sure to uncheck “Match entire cell contents”.

Let’s take this one step at a time.

The following exhibit shows a Word Table. The first table (yellow) is how it looks originally in Word. The second table (green) represents the original table after replacing “^p” with “&xxx&” (in Word). It seems that you have got this far. If this is so, then if you follow the next set of instructions under Excel, with particular attention to item 4, you should be OK.

Book1
	A	B	C	D	E	F
1
2		WordTable(Original)
3		100 250 375 475	250 478 654 587	350	450
4		200 350	275	355	452
5		300 450 480	285	360	453
6		400 620 750 550	295 458 471 698	385	455
7
8		InWord-replace"^p"with"&xxx&"(noquotes)
9		100&xxx&250&xxx&375&xxx&475	250&xxx&478&xxx&654&xxx&587	350	450
10		200&xxx&350	275	355	452
11		300&xxx&450&xxx&480	285	360	453
12		400&xxx&620&xxx&750&xxx&550	295&xxx&458&xxx&471&xxx&698	385	455
13
Sheet1

HTH

Mike

 

[Erol]

multiple lines in 1 cell

I am working on a laptop which means that there is no separate number keypad to the right of the regular keys. When I try to do Alt-0010 it just DING and do not work.
Any suggestion????

thanks