SOLVED

TextJoin keeps adding double quotation marks - How do I make it stop?

%3CLINGO-SUB%20id%3D%22lingo-sub-1535662%22%20slang%3D%22en-US%22%3ETextJoin%20keeps%20adding%20double%20quotation%20marks%20-%20How%20do%20I%20make%20it%20stop%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1535662%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Morning%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETextJoin%20keeps%20adding%20double%20quotation%20marks.%26nbsp%3B%20How%20do%20I%20make%20it%20stop%3F%3C%2FP%3E%3CP%3EThe%20cell%20E3%20contains%20the%20formula%26nbsp%3B%20%3DTEXTJOIN(CHAR(9)%2CTRUE%2CB3%3AD3)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3EB3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20C3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BD3%3C%2FP%3E%3CP%3E1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BLedger%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BLedger%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20create%20a%20string%20with%20these%20joined%20together%20with%20a%20tab%20delimiter.%26nbsp%3B%20When%20I%20copy%20the%20result%20in%20E3%20and%20paste%20into%20my%20other%20application%20Excel%20adds%20double%20quotations%20marks%3A%3C%2FP%3E%3CP%3E%221%20Ledger%20Ledger%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thought%20it%20was%20something%20my%20other%20application%20was%20doing%2C%20but%20even%20pasting%20into%20Notepad%20causes%20the%20double%20quotation%20marks%20to%20appear.%3C%2FP%3E%3CP%3EI%20tried%20using%20Concat%20and%20Concatenate%20instead%20of%20TextJoin%20but%20they%20all%20insist%20on%20adding%20double%20quotation%20marks%20when%20you%20copy%20and%20paste.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHas%20anyone%20found%20a%20work%20around%20for%20this%20bug%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EBarclay%3C%2FP%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-LABS%20id%3D%22lingo-labs-1535662%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1535758%22%20slang%3D%22en-US%22%3ERe%3A%20TextJoin%20keeps%20adding%20double%20quotation%20marks%20-%20How%20do%20I%20make%20it%20stop%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1535758%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F555104%22%20target%3D%22_blank%22%3E%40Barclay0x00%3C%2FA%3E%26nbsp%3B%20depending%20on%20the%20%22other%22%20application%20you%20might%20have%20options%20there%20to%20keep%20source%20formatting%20or%20merge%20formatting%2C%20etc...%26nbsp%3B%20But%20if%20you%20want%20to%20have%20tabs%20added%2C%20you%20might%20be%20better%20off%20just%20having%20the%20text%20in%20consecutive%20columns%20and%20then%20copy%20the%20row%20and%20paste%20it.%26nbsp%3B%20Or%20in%20your%20example%2C%20no%20text%20join%20necessary%2C%20just%20select%20B3%3AD3%20and%20copy%20and%20paste%20(if%20pasting%20into%20word%2C%20select%20paste%20text%20only)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1535941%22%20slang%3D%22en-US%22%3ERe%3A%20TextJoin%20keeps%20adding%20double%20quotation%20marks%20-%20How%20do%20I%20make%20it%20stop%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1535941%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F555104%22%20target%3D%22_blank%22%3E%40Barclay0x00%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20copy%20the%20text%20with%20tabs%20into%20the%20clipboard%20without%20the%20quotes%20that's%20with%20macro.%20Couple%20of%20samples%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.peelonion.com%2F2017%2F03%2Fhow-to-avoid-extra-double-quotes-excel.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.peelonion.com%2F2017%2F03%2Fhow-to-avoid-extra-double-quotes-excel.html%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F24910288%2Fleave-out-quotes-when-copying-from-cell%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F24910288%2Fleave-out-quotes-when-copying-from-cell%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1538592%22%20slang%3D%22en-US%22%3ERe%3A%20TextJoin%20keeps%20adding%20double%20quotation%20marks%20-%20How%20do%20I%20make%20it%20stop%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1538592%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%26nbsp%3B%20Selecting%20the%203%20columns%20and%20doing%20a%20copy%20%2F%20paste%20works%20great.%26nbsp%3B%20I%20had%20been%20using%20the%20TextJoin%20with%20a%20space%20delimiter%20and%20Excel%20doesn't%20add%20the%20double%20quotation%20marks%20for%20a%20space%20delimiter.%26nbsp%3B%20Then%20I%20re-purposed%20the%20code%20by%20just%20substituting%20the%20space%20delimiter%20with%20the%20tab%20character%20and%20the%20whole%20output%20changed!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20some%20experimenting%2C%20it%20seems%20Excel%20wants%20to%20quotation%20anything%20that%20has%20a%20non%20printable%20character%3A%3C%2FP%3E%3CP%3E9%26nbsp%3B%20tab%3C%2FP%3E%3CP%3E10%26nbsp%3B%20line%20feed%3C%2FP%3E%3CP%3E13%20carriage%20return%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EBarclay%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1538596%22%20slang%3D%22en-US%22%3ERe%3A%20TextJoin%20keeps%20adding%20double%20quotation%20marks%20-%20How%20do%20I%20make%20it%20stop%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1538596%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%26nbsp%3B%20This%20is%20a%20good%20work%20around.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EBarclay%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Good Morning,

 

TextJoin keeps adding double quotation marks.  How do I make it stop?

The cell E3 contains the formula  =TEXTJOIN(CHAR(9),TRUE,B3:D3)

 

Example:

B3      C3             D3

1     Ledger     Ledger

 

I want to create a string with these joined together with a tab delimiter.  When I copy the result in E3 and paste into my other application Excel adds double quotations marks:

"1 Ledger Ledger"

 

I thought it was something my other application was doing, but even pasting into Notepad causes the double quotation marks to appear.

I tried using Concat and Concatenate instead of TextJoin but they all insist on adding double quotation marks when you copy and paste.

 

Has anyone found a work around for this bug?

 

Thank you,

Barclay

 

 

 

4 Replies
Highlighted
Best Response confirmed by Barclay0x00 (Occasional Contributor)
Solution

@Barclay0x00  depending on the "other" application you might have options there to keep source formatting or merge formatting, etc...  But if you want to have tabs added, you might be better off just having the text in consecutive columns and then copy the row and paste it.  Or in your example, no text join necessary, just select B3:D3 and copy and paste (if pasting into word, select paste text only)

Highlighted
Highlighted

@mtarler 

 

Thank you!  Selecting the 3 columns and doing a copy / paste works great.  I had been using the TextJoin with a space delimiter and Excel doesn't add the double quotation marks for a space delimiter.  Then I re-purposed the code by just substituting the space delimiter with the tab character and the whole output changed!

 

After some experimenting, it seems Excel wants to quotation anything that has a non printable character:

9  tab

10  line feed

13 carriage return

 

Thank you,

Barclay

 

Highlighted

@Sergei Baklan ,

 

Thank you!  This is a good work around.

 

Thank you,

Barclay