Forum Discussion
TextJoin keeps adding double quotation marks - How do I make it stop?
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
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)
9 Replies
- Just below:
=""""&TEXTJOIN(CHAR(34)&", "&CHAR(34),,H2:H8)&"""" - I have a similar problem with TextJoin - I'm working on Mac, but this seems to behave the same on either Mac or Windows. Because this needs to work on both platforms, VB macros aren't a workable solution.
I'm doing a lot of CONCAT statements to build a configuration script and then running a TEXTJOIN on several of those with a CHAR(10). Sometimes the CONCAT statement includes strings in quotation marks, and when I run a Textjoin it makes those into double quotation marks, and then wraps each cell in the selection with a pair of quotation marks.
As some of these generated configs can get rather lengthy, what ends up needing to be done is pasting it into a text editor like Sublime Text and initially doing a bulk search and replace on double quotes and replacing them with another temporary character, and then replacing single quotes with nothing, and then replacing the temporary characters with single quotes. But I have to be sure the temp character is something not used anywhere else in the document.
So how the heck can I do this without the quotes? I'm sure this behaviour of TEXTJOIN exists for some obscure use case, but it's really annoying.
Yes, I know, Excel is meant for numbers.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.
To copy the text with tabs into the clipboard without the quotes that's with macro. Couple of samples
https://www.peelonion.com/2017/03/how-to-avoid-extra-double-quotes-excel.html
https://stackoverflow.com/questions/24910288/leave-out-quotes-when-copying-from-cell
- This Crazy why on earth would mircosoft do this?
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)
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
