23

My question is similar to Copying a multiline text into a single cell in Excel, except that the text I want to copy contains rich text formatting - bold text, colored text, bullet items, etc...

If I double-click in the cell, or click in the formula bar, before pasting, as directed in the linked question's answer, it does preserve the line breaks, but all other formatting is lost; it treats the paste as a plaintext paste.

Is there any way to copy formatted, multiline text from a web page, an email, MS Word, or any other program, into a single cell in Excel?

As an example, suppose I want to copy the following text directly out of this web page, and paste it all into one cell of my workbook, while preserving the line breaks and formatting:

How to make a sandwich:

  • 2 slices of bread
  • one slice of meat
  • one slice of cheese

Put the meat on one of the bread slices. Put the cheese on top of that. Put the other bread slice on top of that.
Voila!

Here are several screenshots of ways I tried to accomplish this by copying from a table in a Microsoft Word document into Excel using various options. The text A1, B1, etc. indicate the cells I expect the text to end up in.

In Word, with formatting characters displayed:
In MS Word

Using default Paste into Excel in cell A1. Note that Excel broke the text at both the hard (¶) and soft returns (↵) into separate rows, and created merged cells for the "A1" and "C1" text:
Default Paste

Using "Match Destination Formatting" paste option into Excel, in cell A1:
Match Destination Formatting Paste

Double-clicking inside cell B1, then pasting (note that no "Paste Special" options are available with this approach):
Click inside cell, then paste

And here, I noted all of the character formatting Excel applied when using the default paste, and manually applied it to the text in order to illustrate my desired result. Excel does not offer bullet formatting, but when using the default paste, it puts the bullet symbol in 11.5 pt Symbol font, and reproduces the Tab from Word with several space characters in 7 pt Times New Roman. The only thing that can't be applied to a subset of the text in a cell is the indented alignment, but even without that, to get a paste result like this, without having to manually apply the font, size, bold, and italics everywhere, every time (and color, if applicable), would be ideal:
Manual creation of desired result

11
  • Right click cell and use "paste options" > "formatting" or "paste special". See Paste Options
    – DavidPostill
    Sep 30, 2015 at 19:20
  • 1
    @DavidPostill I've added an example. If what you're suggesting works with this example text, please turn your comment into an answer. (Hint: it doesn't.) Sep 30, 2015 at 22:35
  • Hmm. It should. What happens when you try it? Note I don't have a copy of Office any more to test it on.
    – DavidPostill
    Oct 1, 2015 at 7:14
  • 1
    @DavidPostill the formatted text gets pasted, but line breaks result in the text being split over multiple rows in the spreadsheet. See the linked question. Oct 1, 2015 at 7:17
  • 2
    There is a macro in this post which looks like it could help you Importing Word Paragraph to Excel - 1 cell, same formating -- I lose the format
    – DavidPostill
    Oct 1, 2015 at 8:01

5 Answers 5

2

I've been looking and looking for answers to exactly this problem: I have a formatted Word doc I need to use as front matter to my spreadsheet, and I just desperately wanted to copy paste the data in easily. The Word Object option doesn't let you click the hyperlink unless you open the embedded Word file, so the initially visible links aren't clickable. I finally stumbled upon the solution all on my own: use a text box. Why do you really need a cell for stuff like this? Just use a text box.

1

Easy solution:

  1. Convert your formatted text in MS Word to a table. Select the text, from the Insert tab > Table > Convert Text to Table. Remember that any text after a (hard) return in your text will form a new table row. You can replace them with soft returns (shift enter) if you like.

    1. Copy your MS Word table cells into Excel.

    2. Voila!

3
  • 4
    Will this work with the sample text I provided above, to get into one cell? I suspect not, as the bullets require a hard return. Nov 22, 2015 at 20:44
  • 6
    I tested this, and confirm that it does not work. Even if I create a 1x1 table in Word, and then paste my formatted text inside that table cell, I still can't copy it out and paste it into Excel and get it to all land in one cell, while preserving line breaks, bullets, bold, and italics. I can either get it into one cell, with line breaks, but lose all other formatting, or I can retain all formatting, but have each line of text land in a new row. Nov 23, 2015 at 17:28
  • 1
    Even if you hand format it to all be in a single cell, when you paste it into excel, it ignores the word table and just splits everything into multiple lines. Dude didn't even test his answer. May 9, 2022 at 12:09
1

I have been wanting to do this for a long time too.

I started playing around with Excel and noticed the Insert Word Object will do what you want.

Here is a link that explains the different ways to Insert Objects.

https://support.office.com/en-gb/article/insert-an-object-in-your-excel-spreadsheet-e73867b2-2988-4116-8d85-f5769ea435ba

The "Create a new object from inside Excel" appears to be what you want.

The one draw back I see is the Cell will not auto-resize when the Object size changes so you will have to manually resize the cell size.

0
  1. Select the target table in Word.
  2. Press Ctrl+H to open 'Find and Replace' dialog box.
  3. Type ^p in 'Find what:' text box and in 'Replace with:' text box, type some unique text or character that does not occur in the text in your table, like ~~~ or |
  4. Press Replace All button.
  5. Close the 'Find and Replace' dialog box.
  6. Copy the table in Word and Paste it into Excel.
  7. Now press Ctrl+H in Excel to open 'Find and Replace' dialog box.
  8. Enter ~~~ or | (or whatever you used in Word table) in 'Find what:' text box and place cursor in 'Replace with:' text box and press Alt+0010.
  9. Press Options>> button and ensure that the 'Match case' and 'Match entire cell contents' check boxes are not checked.
  10. Press Replace All button.
1
  • 1
    I was hopeful with this solution. It does help with the multi-line issue but the final Find/Replace removes all inline formatting. Bold, italic, and colored text are lost. The formatting of the first character in the cell is copied to all other cells. You could use VBA to perform Find/Replace, like this function, but it has a limitation that if a cell has more than 255 characters no changes will be made to the cell and I could not find a work around.
    – Ben
    Aug 6, 2020 at 15:33
-1

Open the PDF. Select the text required and Copy (to clipboard). (Ctrl c) Open Excel. Select the cell you want to paste into. Don't paste into the cell, but paste into the Formula bar window. (Ctrl v) Your text should now appear in your selected Cell. Any Carriage Returns (CR) will now start a new line within the cell. If you want another CR within the cell, press Alt Return where you want the CR. Otherwise Return just selects the next cell. If you want to "lose" a CR, just delete like any other character. Hope this helps.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .