Copy/Paste multiple line cells results in double quotes

dhancy

Board Regular
Joined
Jul 10, 2013
Messages
107
I have a spreadsheet with a single column and several hundred rows. Some of those cells (not all) have text on multiple lines. For instance:

abc
xyz

When I copy/paste all cells in this column into something like notepad, those with multiple lines are surrounded by double quotes and look like this:

"abc
xyz"


How can I prevent the double quotes from showing in the resulting document?

Thank you!

Dennis
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Try putting this formula in adjacent column and paste returned values into Notepad
Excel Formula:
=TRIM(SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)," "),CHAR(13)," "))

It will probably get rid of all the double quotes but are you happy with the resultant values in Notepad?
 

dhancy

Board Regular
Joined
Jul 10, 2013
Messages
107
Try putting this formula in adjacent column and paste returned values into Notepad
Excel Formula:
=TRIM(SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)," "),CHAR(13)," "))

It will probably get rid of all the double quotes but are you happy with the resultant values in Notepad?
Thanks for your reply!

The good news is that the double quotes are gone. But now both lines appear on one line.
Is there anyway to preserve the multiple lines and not have the quotes?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Another way ...
- paste original data (with resultant double quotes) to Notepad
- (in Notepad) replace quotes with nothing (Edit \ Replace )

Search Replace.jpg


If there are double quotes in Excel to be retained
... (in Excel) replace double quotes with XXXX
... paste to Notepad
... replace auto-generated double quotes with nothing (as above)
... replace XXXX with double quote
 

dhancy

Board Regular
Joined
Jul 10, 2013
Messages
107
Another way ...
- paste original data (with resultant double quotes) to Notepad
- (in Notepad) replace quotes with nothing (Edit \ Replace )

View attachment 33029

If there are double quotes in Excel to be retained
... (in Excel) replace double quotes with XXXX
... paste to Notepad
... replace auto-generated double quotes with nothing (as above)
... replace XXXX with double quote

Well I discovered one thing. If I paste this into Word, there are no double quotes.

For my immediate purposes, this works great, as I don't need to use notepad.
 

Forum statistics

Threads
1,148,193
Messages
5,745,272
Members
423,941
Latest member
CluelessAboutExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top