How can I prevent Excel from Adding extra quote marks when I paste information out of excel

psycoperl

Active Member
Joined
Oct 23, 2007
Messages
287
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
I am working on a project where I am building HTML Code for link lists in Excel and then will copy and paste the completed code into my HTML document. However, I am noticing that when I copy and paste the resulting HTML code out of excel and into any text editor (including the Mr.Excel Forum's Code Editor) that it pre-appends and appends a " at the ends of the string and then doubles any " that are already existing in the code. How can I stop these extra " marks from being added to my string when I cut and paste?


Cell Formulas
RangeFormula
J2J2=CONCATENATE(HTMLCODE!A1,I2,HTMLCODE!A2,F2,HTMLCODE!A3,HTMLCODE!A4,HTMLCODE!A5,HTMLCODE!A6,I2,HTMLCODE!A7)


_FileList.xlsx
A
1<p style="padding-left:20pt;"><a href="
2" target="_blank">
3</a><br />
4
5
6<span style="font-size:10pt;word-break:break-all;margin-left:10pt;margin-right:20pt;display:block;"><em>
7</em></span></p>
8
HTMLCODE
Cell Formulas
RangeFormula
A4,A8A4=CHAR(10)
A5A5=CONCATENATE(CHAR(9),CHAR(9),CHAR(9))
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,373
Office Version
  1. 2016
Platform
  1. Windows
Hi Psycoperl,

It's your text qualifier. Type some text in a cell and select that cell.
Data, Text to Columns, Delimited, Next, under Text Qualifier select None then Finish.

Your cell should no longer be prefixed/suffixed by double quotes when you paste into another app.
 

psycoperl

Active Member
Joined
Oct 23, 2007
Messages
287
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
Ok would this change it just for this file or would it be for all files?
 

psycoperl

Active Member
Joined
Oct 23, 2007
Messages
287
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
Unfortunately -- this did not work.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,373
Office Version
  1. 2016
Platform
  1. Windows
Sorry, I was pasting the wrong cell.

It's the CHAR(10) and =CONCATENATE(CHAR(9),CHAR(9),CHAR(9)) which makes Excel wrap it in double quotes.
I can't see a way to avoid that if the string must contain control characters.
 
Solution

psycoperl

Active Member
Joined
Oct 23, 2007
Messages
287
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
Sorry, I was pasting the wrong cell.

It's the CHAR(10) and =CONCATENATE(CHAR(9),CHAR(9),CHAR(9)) which makes Excel wrap it in double quotes.
I can't see a way to avoid that if the string must contain control characters.
Ok. thank you for the guidance. I used the app BBEDIT to clean up the Prepending and Appending quote marks as it has a function to clean up lines of code.

I will try to remember this going forward.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,105
Messages
5,622,762
Members
415,926
Latest member
jerrynababa

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