Excel: Paired Double Quotation Marks Showing on Formula Export


I am using a formula with char(10) to create carriage returns in output text. This seems to be having a side effect of duplicating my quotation marks - changing " to "".

This does not show in Excel, but only shows when the values are pasted into notepad. Is there a way to format my Excel formula so it does not display the paired double quotation marks?

See attached spreadsheet for my examples.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Yes, the issue is not with your formula but the formatting of text moving and moving from Excel to Notepad.  If you paste into Word, you could paste as plain text and get your content without the quotes.  You can try CONCATENATE() instead of & but don't think that part matters for what you are seeing.

Here is a workaround I use in situations I need to go to NotePad (and cannot use some other tool like Word, TextPad, et cetera).

In a Windows run command or the "Search the web and Windows" box on Windows 10, you can paste the text...this will strip any formatting.  Cut the text from there, then paste into NotePad --- no quotes.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProfessorJimJamMicrosoft Excel ExpertCommented:
like Kevin has mentioned, if you paste this into the word it will not have this issue, however if you insist in using notepad, then you can use a different way of copying.

you can put this below code in your personal.xlsb file also add the reference of Microsoft Forms 2.0 from the Reference and then assign a shortcut key to this macro and then whenever you copy cell instead of control + C  you use the shortcut key to this macro lets say for example Control + Q   then when use this, and paste in notebook, it will work and it will paste in notepad without the double quotes.

Sub CopyNewVer()
'create a reference in the VBE to Microsft Forms 2.0 Lib
Dim oo As New DataObject
Dim str As String
strTemp = ActiveCell.Value
oo.SetText (str)
End Sub

Open in new window

dabug80Author Commented:
Thanks for the help. I will use the MS Word solution. I also awarded support points to the Professor as it's a solid work around that could be combined with a button or further macro to ensure best formatting.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.