Home

Extra and "double" double quotes when using carriage return in formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-166337%22%20slang%3D%22en-US%22%3EExtra%20and%20%22double%22%20double%20quotes%20when%20using%20carriage%20return%20in%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-166337%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20all.%3C%2FP%3E%0A%3CP%3EI%20am%20attempting%20to%20copy%20Excel%20cell%20contents%20in%20a%20text%20file%20(for%20further%20use%20later).%3C%2FP%3E%0A%3CP%3EHowever%2C%20when%20copying%20and%20pasting%20cells%20with%20formulas%20that%20contain%20both%20quotes%20(%22%22)%20and%20carriage%20returns%26nbsp%3B%3CSPAN%3E(either%26nbsp%3B%3C%2FSPAN%3E%3CEM%3ECHAR(13)%3C%2FEM%3E%3CSPAN%3E%26nbsp%3Bor%20%22%3C%2FSPAN%3E%3CEM%3EAlt%2BEnter%3C%2FEM%3E%3CSPAN%3E%22)%2C%20the%20result%20contains%20%22extra%22%20quotes%20at%20odd%20places.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20if%20formula%20is%26nbsp%3B%3CFONT%20color%3D%22%230000FF%22%3E%3CEM%3E%3D%5B%40Type%5D%20%26amp%3B%20%5B%40%5BCallout%20Pipe%201%5D%5D%3C%2FEM%3E%3C%2FFONT%3E%20(with%20no%20carriage%20returns)%2C%20the%20result%20is%20(after%20pasting%20into%20Notepad)%3A%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%23339966%22%3E%3CEM%3ESDMH12%22%20CPP%20N%20IE%3D%20320.8%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3EJust%20as%20desired.%20However%2C%20we%20need%20a%20carriage%20return%20after%20%22SDMH%22%20(from%20column%20%3CEM%3E%5B%40Type%5D%3C%2FEM%3E)%2C%20so%20if%20we%20simply%20add%20a%20carriage%20return%20(again%2C%20%3CSPAN%3Eeither%26nbsp%3B%3C%2FSPAN%3E%3CEM%3ECHAR(13)%3C%2FEM%3E%3CSPAN%3E%26nbsp%3Bor%20%22%3C%2FSPAN%3E%3CEM%3EAlt%2BEnter%3C%2FEM%3E%3CSPAN%3E%22)%20with%20this%20formula%26nbsp%3B%3CFONT%20color%3D%22%230000FF%22%3E%3CEM%3E%3D%5B%40Type%5D%20%3CSTRONG%3E%26amp%3B%20CHAR(13)%3C%2FSTRONG%3E%20%26amp%3B%20%5B%40%5BCallout%20Pipe%201%5D%5D%3C%2FEM%3E%3C%2FFONT%3E%2C%20pasting%20it%20in%20Notepad%20results%20in%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%23339966%22%3E%3CEM%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E%22%3C%2FSTRONG%3E%3C%2FFONT%3ESDMH%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%23339966%22%3E%3CEM%3E12%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E%22%22%3C%2FSTRONG%3E%3C%2FFONT%3E%20CPP%20N%20IE%3D%20320.8%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E%22%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EAs%26nbsp%3Bcan%20be%20seen%2C%20the%20return%20carriage%20worked%2C%20but%20my%20text%20now%20has%20three%20extra%20double%20quotes%20that%20weren't%20meant%20to%20be%20there.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ECould%20anyone%20help%20me%20solve%20this%20problem%3F%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EThanks%20in%20advance%2C%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EEdgar%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-166337%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-728430%22%20slang%3D%22en-US%22%3ERe%3A%20Extra%20and%20%22double%22%20double%20quotes%20when%20using%20carriage%20return%20in%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-728430%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F123651%22%20target%3D%22_blank%22%3E%40Edgar%20Soares%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20all%3C%2FP%3E%3CP%3EI%20have%20the%20same%20issue.%20I%20need%20copy%20data%20from%20Excel%20to%20an%20ERP%20using%20copy%2Fpaste%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA1%20%3D%20AA%3C%2FP%3E%3CP%3EB2%20%3D%20BB%3C%2FP%3E%3CP%3EA1%26amp%3BA2%20is%20ok%20in%20Notepad%2B%2B%26nbsp%3B%26nbsp%3B%20%3A%20AABB%3C%2FP%3E%3CP%3Ewith%20A1%26amp%3Bchar(9)%26amp%3BB2%20I%20have%20%22AA%20-%26gt%3BBB%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20also%20searching%20how%20to%20remove%20the%20double%20quotes%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F123651%22%20target%3D%22_blank%22%3E%40Edgar%20Soares%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3EHi%2C%20all.%3C%2FP%3E%3CP%3EI%20am%20attempting%20to%20copy%20Excel%20cell%20contents%20in%20a%20text%20file%20(for%20further%20use%20later).%3C%2FP%3E%3CP%3EHowever%2C%20when%20copying%20and%20pasting%20cells%20with%20formulas%20that%20contain%20both%20quotes%20(%22%22)%20and%20carriage%20returns%26nbsp%3B%3CSPAN%3E(either%26nbsp%3B%3C%2FSPAN%3E%3CEM%3ECHAR(13)%3C%2FEM%3E%3CSPAN%3E%26nbsp%3Bor%20%22%3C%2FSPAN%3E%3CEM%3EAlt%2BEnter%3C%2FEM%3E%3CSPAN%3E%22)%2C%20the%20result%20contains%20%22extra%22%20quotes%20at%20odd%20places.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EFor%20example%2C%20if%20formula%20is%26nbsp%3B%3CFONT%20color%3D%22%230000FF%22%3E%3CEM%3E%3D%5B%40Type%5D%20%26amp%3B%20%5B%40%5BCallout%20Pipe%201%5D%5D%3C%2FEM%3E%3C%2FFONT%3E%20(with%20no%20carriage%20returns)%2C%20the%20result%20is%20(after%20pasting%20into%20Notepad)%3A%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23339966%22%3E%3CEM%3ESDMH12%22%20CPP%20N%20IE%3D%20320.8%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3EJust%20as%20desired.%20However%2C%20we%20need%20a%20carriage%20return%20after%20%22SDMH%22%20(from%20column%20%3CEM%3E%5B%40Type%5D%3C%2FEM%3E)%2C%20so%20if%20we%20simply%20add%20a%20carriage%20return%20(again%2C%20%3CSPAN%3Eeither%26nbsp%3B%3C%2FSPAN%3E%3CEM%3ECHAR(13)%3C%2FEM%3E%3CSPAN%3E%26nbsp%3Bor%20%22%3C%2FSPAN%3E%3CEM%3EAlt%2BEnter%3C%2FEM%3E%3CSPAN%3E%22)%20with%20this%20formula%26nbsp%3B%3CFONT%20color%3D%22%230000FF%22%3E%3CEM%3E%3D%5B%40Type%5D%20%3CSTRONG%3E%26amp%3B%20CHAR(13)%3C%2FSTRONG%3E%20%26amp%3B%20%5B%40%5BCallout%20Pipe%201%5D%5D%3C%2FEM%3E%3C%2FFONT%3E%2C%20pasting%20it%20in%20Notepad%20results%20in%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23339966%22%3E%3CEM%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E%22%3C%2FSTRONG%3E%3C%2FFONT%3ESDMH%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23339966%22%3E%3CEM%3E12%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E%22%22%3C%2FSTRONG%3E%3C%2FFONT%3E%20CPP%20N%20IE%3D%20320.8%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E%22%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EAs%26nbsp%3Bcan%20be%20seen%2C%20the%20return%20carriage%20worked%2C%20but%20my%20text%20now%20has%20three%20extra%20double%20quotes%20that%20weren't%20meant%20to%20be%20there.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ECould%20anyone%20help%20me%20solve%20this%20problem%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%20in%20advance%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EEdgar%3C%2FSPAN%3E%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-729221%22%20slang%3D%22en-US%22%3ERe%3A%20Extra%20and%20%22double%22%20double%20quotes%20when%20using%20carriage%20return%20in%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-729221%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F123651%22%20target%3D%22_blank%22%3E%40Edgar%20Soares%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Edgar%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20only%20using%20workarounds.%20It%20could%20be%20macro%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexcel.tips.net%2FT002237_Getting_Rid_of_Extra_Quote_Marks_in_Exported_Text_Files.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexcel.tips.net%2FT002237_Getting_Rid_of_Extra_Quote_Marks_in_Exported_Text_Files.html%3C%2FA%3E.%20Or%20copy%20paste%20through%20Word%2C%20or%20first%20paste%20as%20value%20into%20another%20cell%2C%20copy%20it%20content%20in%20edit%20mode%20and%20paste%20into%20Notepad%20then.%20Perhaps%20something%20else.%20That's%20lot%20of%20posts%20about%20the%20issue%2C%20e.g.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsuperuser.com%2Fquestions%2F324271%2Fhow-to-copy-multi-line-text-from-excel-without-quotes%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsuperuser.com%2Fquestions%2F324271%2Fhow-to-copy-multi-line-text-from-excel-without-quotes%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Edgar Soares
New Contributor

Hi, all.

I am attempting to copy Excel cell contents in a text file (for further use later).

However, when copying and pasting cells with formulas that contain both quotes ("") and carriage returns (either CHAR(13) or "Alt+Enter"), the result contains "extra" quotes at odd places.

For example, if formula is =[@Type] & [@[Callout Pipe 1]] (with no carriage returns), the result is (after pasting into Notepad):

SDMH12" CPP N IE= 320.8

Just as desired. However, we need a carriage return after "SDMH" (from column [@Type]), so if we simply add a carriage return (again, either CHAR(13) or "Alt+Enter") with this formula =[@Type] & CHAR(13) & [@[Callout Pipe 1]], pasting it in Notepad results in:

"SDMH

12"" CPP N IE= 320.8"

As can be seen, the return carriage worked, but my text now has three extra double quotes that weren't meant to be there.

Could anyone help me solve this problem?

Thanks in advance,

Edgar

2 Replies

@Edgar Soares

 

Hi all

I have the same issue. I need copy data from Excel to an ERP using copy/paste

 

A1 = AA

B2 = BB

A1&A2 is ok in Notepad++   : AABB

with A1&char(9)&B2 I have "AA ->BB"

 

I am also searching how to remove the double quotes

 

Thanks for help

 


@Edgar Soares wrote:

Hi, all.

I am attempting to copy Excel cell contents in a text file (for further use later).

However, when copying and pasting cells with formulas that contain both quotes ("") and carriage returns (either CHAR(13) or "Alt+Enter"), the result contains "extra" quotes at odd places.

For example, if formula is =[@Type] & [@[Callout Pipe 1]] (with no carriage returns), the result is (after pasting into Notepad):

SDMH12" CPP N IE= 320.8

Just as desired. However, we need a carriage return after "SDMH" (from column [@Type]), so if we simply add a carriage return (again, either CHAR(13) or "Alt+Enter") with this formula =[@Type] & CHAR(13) & [@[Callout Pipe 1]], pasting it in Notepad results in:

"SDMH

12"" CPP N IE= 320.8"

As can be seen, the return carriage worked, but my text now has three extra double quotes that weren't meant to be there.

Could anyone help me solve this problem?

Thanks in advance,

Edgar


 

 

 

@Edgar Soares 

 

Hi Edgar,

 

That's only using workarounds. It could be macro https://excel.tips.net/T002237_Getting_Rid_of_Extra_Quote_Marks_in_Exported_Text_Files.html. Or copy paste through Word, or first paste as value into another cell, copy it content in edit mode and paste into Notepad then. Perhaps something else. That's lot of posts about the issue, e.g. https://superuser.com/questions/324271/how-to-copy-multi-line-text-from-excel-without-quotes