8

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

  • Right click cell and use "paste options" > "formatting" or "paste special". See Paste Options – DavidPostill Sep 30 '15 at 19:20
  • @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.) – Dan Henderson Sep 30 '15 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 '15 at 7:14
  • @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. – Dan Henderson Oct 1 '15 at 7:17
  • Does Double-click first then Right click cell and use "paste options" > "formatting" or "paste special" work? – DavidPostill Oct 1 '15 at 7:20
2

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!

  • 1
    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. – Dan Henderson Nov 22 '15 at 20:44
  • 1
    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. – Dan Henderson Nov 23 '15 at 17:28

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.