Share via

Why is Excel adding quotes when I copy a cell with lines breaks???

Anonymous
2023-09-09T13:49:20+00:00

ChristianHWT - copied the below from ChristianHWT, i see that thread is closed and I am not sure why, this is a very relevant question causing a lot of frustration, please can Microsoft developers put a fix for this, i am using microsoft 365 and this is truly painful.

Hi,

I stumbled upon this problem recently and found that there are loads of people on the Internet having the same problem:

I have a cell with hard line breaks (not automatic word wrapping). When I copy & paste it to outside of Excel, double quotes are added to the beginning and end of the cell's content.

For example, the content

Test

123

will be pasted elsewhere as

"Test

123"

This is in my opinion a totally useless thing to do (no other program I ever used was doing that to text with line breaks) and ruins a calculation tool which I was creating, from where a resulting "report" should be copied to a web interface.

There are 2 accepted "solutions" on the Internet:

  1. Use Word as an intermediary, so copy the text to Word first and then to the target. That's an inefficient workaround at best.
  2. Use a Macro. I can't do that because of restrictions on documents containing Macros.

Since both solutions are not really acceptable for me, I did some experiments, using the CHAR() function to add other non-printable characters into the text instead of the line breaks. I found that Excel adds the quotes only to strings containing the non-printable characters 10 (line feed), 13 (carriage return) and 9 (tab). All other non-printable characters, such as 12 (form feed) or 30 (record separator) do NOT cause Excel to add quotes.

Obviously that doesn't help me since these characters do not create line breaks in my report, but it shows that some software developer working on Excel already some 20 years ago decided intentionally to add quotes to strings containing this 3 non-printable characters. And none of his successors working on any of the newer versions decided to change that...

Therefore my question now is: Why on earth is Excel doing this? Why? What reason could there be to do this? I really don't get it.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-09-10T07:57:25+00:00

    i cannot go into edit mode, this is because if I copy in edit mode, it will copy the formula not the result of the formula.

    My formula contains concat, char(10), etc

    When I paste into word and then into notepad, it works. There should be a better way to do this, i.e. paste from i.e.

    copy from excel and paste into notepad

    not

    copy from excel and paste into word, then copy from word, then paste into notepad.

    I think this needs to be investigated by microsoft excel devs, we DONT want WORKAROUNDS, we need this to just work. PLEASE

    Also many people are frustrated about this in multiple forums. So it is a legit issue.

    30+ people found this answer helpful.
    0 comments
  2. Anonymous
    2023-09-10T01:10:12+00:00

    This issue occurs because Excel uses double quotes to indicate the start and end of a cell's content. When you copy a cell with hard line breaks, Excel adds double quotes to the beginning and end of the cell's content to ensure that the line breaks are preserved when pasted outside of Excel.

    To avoid this issue, you can try copying the cell's content in Edit mode ( double click the cell or use F2)

    If you want to copy mutiple cells with link break. As you mentioned, the only way is to copy to word application and then copy it from word to another applition.

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2023-09-10T08:07:00+00:00

    1 person found this answer helpful.
    0 comments