Can I paste rich text and preserve formatting?

Question

I have an Excel sheet and I paste text into a text box. I'm trying to copy+paste a source code from Eclipse and to preserve syntax highlighting and preserve tabs (code formatting).

Syntax highlighting - works fine by using: context menu -> Paste options -> Keep Source Formatting. But this options ruins my code formatting because I can see that excel removes automatically all leading tabs, so my code, pasted into excel text box, is not formatted anymore.

Anyone knows how to preserve code formatting while pasting into text box?

Answer 1

I just tested this with Visual Studio so it may slightly different from Eclipse. When you say text box I also assume you mean a single Excel cell. From Visual Studio Excel would only allow pasting of text. I then pasted the code into Word which preserves formatting and the highlighting. Copying the text from Word and then pasting into Excel seemed to preserve both the syntax and format.

I will say that while Excel can do a bunch of things it may not be the best tool to use for everything. It's hard to say without knowing more about your specific case but another tool might be better for preserving code format.

Answer 2

So it appears that only TAB characters are lost when pasting and SPACE characters are preserved. This isn't so much as an answer but a workaround which involves moving the code/query to Notepad++ (or similar), performing a regular expression replacement to convert leading tabs into spaces and then pasting the revised text to Excel.

1. Copy code/query into Notepad++ (or similar)
2. Do a REPLACE (CTRL+H) with the following regular expression settings:
   • Find what: **^(\s)*(\t)**
   • Replace with: **\1**...
     (where the three dots are actually a sequence of spaces that you want to replace each tab with)
   • Regular expression (checked)

You need to do a "Replace All" multiple times as it will only replace one leading tab per line per run so if your code has 4 indents then run "Replace All" 4 or more times.

Now paste the revised text into the Excel text box and it will honour the (space) indents.

Answer 3

It seems TAB cannot be visualized in Excel cell, even if successfully pasted. It also seems it cannot be inserted inside cell (e.g. by pressing "Tab" keyboard key): https://answers.microsoft.com/en-us/msoffice/forum/all/how-to-insert-a-tab-inside-a-cell/2f189cfe-ef90-4113-9d22-135ac010359f.

Using "Formula Bar" as a preview for your code can be a way to consider, as it preserves TAB:

however, by pasting code into one cell, you loose syntax highlighting.