15

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
  • 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 '15 at 8:01
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!

| improve this answer | |
  • 3
    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
  • 4
    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
1
  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.
| improve this answer | |
0

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.

| improve this answer | |
-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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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