Jul 21 2020 06:18 AM
Jul 21 2020 06:18 AM
TextJoin keeps adding double quotation marks. How do I make it stop?
The cell E3 contains the formula =TEXTJOIN(CHAR(9),TRUE,B3:D3)
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?
Jul 21 2020 06:57 AMSolution
@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)
Jul 21 2020 07:54 AM
To copy the text with tabs into the clipboard without the quotes that's with macro. Couple of samples
Jul 22 2020 08:20 AM
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:
10 line feed
13 carriage return
Jul 22 2020 08:21 AM
Thank you! This is a good work around.
Oct 21 2021 11:52 AM
Oct 21 2021 02:43 PM
That's not the formula, TEXTJOIN or any other, that's how Excel works with the clipboard. You may manually enter into the cell any text with special characters (e.g. multi-line text using Alt+Enter), copy this cell and expand clipboard pane - you will see such text within it quotes. Moving text to the clipboard Excel applies CSV standard, it says text with special symbols shall be quoted, otherwise it could be not parsed correctly in another application.
Workaround is to use macros to play with clipboard.