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:
Best Answer
Easy solution:
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.
Copy your MS Word table cells into Excel.
Voila!