Copy multiline formatted text into a single cell in Excel

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:

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:

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

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

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:

Solution 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.

   2. Copy your MS Word table cells into Excel.

   3. Voila!

Solution 2:

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.

Solution 3:

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.