How to copy a cell containing line breaks in Excel as is

GoogleAdsense広告
[広告, Ads, 广告]

(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.

  1. If text is entered directly into a cell:
    Double-click on the cell to copy the text.
  2. 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.).
*** Caution ***
  • You may get different results depending on the software version, computer equipment configuration, etc.

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)…

Excel image with text containing line breaks and double quotation marks in cells

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.).

Image of text pasted into the editor

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.

Excel image of text, including line breaks and double quotation marks, on a gray background, extending down the cell

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.

Image of text pasted into the editor

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).

Excel image with text containing double quotation marks in a cell. No apparent line breaks.

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.

Excel image with “=html()” in the cell

Paste as “value” into another blank cell (1. Right-click on the cell, 2. Click on Paste “Value”).

An Excel image showing the “123” icon and other icons in the context menu. The “123” icon is surrounded by a red frame as a click target.

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

Others

脚注

  1. “Ctrl+C” is the action of pressing the “c” key while holding down the “Ctrl” key. The same applies to the following.
タイトルとURLをコピーしました