(Machine translation by DeepL + adjustments by Gyegyopon, Translated article written for Japan without major changes, Sorry some Japanese language is still there)
Hello, my name is Gyegyopon.
In Excel, if a cell contains a line break, the text string cannot be copied verbatim and unwanted double quotation marks are automatically added.
This article summarizes under what circumstances this occurs and how to deal with it.
I hope this article will be helpful to those who are having similar problems.
Main text
Summary
Problem
In Excel, if a cell contains a line break, copying and pasting the cell into a text editor, etc. will automatically add an unnecessary “ (double quotation mark).
(Double quotation marks will be added at the beginning and end of the text, increasing the number of double quotation marks in the middle to two each.)
*HTML code often includes double quotation marks and may includes line breaks to make the code easier to read. Therefore, you may often run into this problem when dealing with HTML code in Excel.
Methods
You can avoid adding unnecessary double quotation marks by using the following method.
*It may be an obvious way to deal with the problem once you know what to do.
- If text is entered directly into a cell:
Double-click on the cell to copy the text. - If no text is entered directly into the cell (e.g., if a function is used):
Copy and paste the contents of a cell once into another cell as a “value” and then copy the contents of that cell using the method (1.).
I don’t know if it’s the best
Gyegyopon does not mean to imply that the method described on this page is the least time-consuming, but rather that it is one of the options available.
Someone might think it is easier to copy and paste as usual, delete the leading and trailing double quotation marks, and replace the two consecutive double quotation marks in the middle with a single one, using an editor’s replace function or a macro (program).
I wish there was an easier way to copy the text verbatim, but so far I have not been able to find it.
Problem Details
If a cell in Excel contains line breaks, copying in the general way will automatically add unnecessary double quotation marks.
If a cell in Excel contains text with line breaks, copy the contents of the cell (1. Click the cell and press Ctrl+C1)…
When pasted into a text editor, etc. (1. click Ctrl+V), double quotation marks will be added before and after the text. Also, all the original double quotation marks will become two (2.).
Method 1 (when text is entered directly into a cell)
If text is entered directly into a cell, the automatic addition of double quotation marks can be avoided by following the steps below.
Double-click on the cell. A cursor will appear in the cell.
Then press Ctrl+A (Select All). The text background will turn gray.
Press Ctrl+C (Copy) again. The text is now copied to the clipboard.
Click on the text editor and paste the text (1. Click and Ctrl+V).
The text has been copied without unnecessary double quotation marks being added.
Method 2 (when text is not entered directly into the cell)
If no text is entered directly into a cell, the following procedure will avoid the automatic addition of double quotation marks.
(“No text is entered directly into the cell” is when a function is used, for example.)
Copy the text (1. Click on the cell and press Ctrl+C).
If you double-click a cell (1.) (as in “Method 1”), the functions, etc. entered in the cell will be displayed (2.), and you will not be able to copy the text.
Paste as “value” into another blank cell (1. Right-click on the cell, 2. Click on Paste “Value”).
Execute “ Method 1 ” in the cell where you pasted the text.
Environment
Software
- OS: Windors 10 Pro Version 23H2
- Excel: Microsoft Excel for Microsoft 365 MSO (Version 2410 Build 16.0.18129.20100) 64 bit
- Text editor: “Notepad” included with Windows 11
Others
- Implementation date: November 2024
Related Information
Reference
- Microsoft: Keyboard shortcuts in Excel
This page provides a summary of keyboard shortcuts (easy operations) such as Ctrl+C.
