char(10) and double quotes

[JohnE]

When I put in the following, I get the expected result in the cell. However,
when I cut and paste the cell value to notepad or any other text editor I get
double quote marks around text.

="line1"&CHAR(10)&"line2"

Shows no double quotes markes in Excel, but cut and paste to notepad shows
double quote marks:

"line1
line2"

Is there a way to prevent these superfluous double quote marks when doing a
cut/paste to a text editor?

 

[JLatham]

Probably not easily - I duplicated your situation. Also when I put
line1 in cell A4
line2 in cell A5 and in
A6 put the formula =A4 & Char(10) & A5
the result over in Notepad was the same: "line1 [squarebox] line2"

 

[Dave Peterson]

You could use a macro.

Saved from a previous post:

I used the PutInClipboard routine that Chip Pearson has:
http://www.cpearson.com/excel/clipboard.htm

With this sub:
Option Explicit
Sub testme()

Dim MyDataObj As DataObject
Set MyDataObj = New DataObject

MyDataObj.SetText ActiveCell.Text
MyDataObj.PutInClipboard

End Sub

And then pasted (manually) into NotePad. No double quotes were inserted. But I
did see a little square where the alt-enter was.

Chip has instructions that you have to follow (including the tools|references
with "Microsoft Forms 2.0 object library") on that sheet.

=========

If you wanted to copy multiple cells, this may help you:

Option Explicit
Sub testme()

Dim MyDataObj As DataObject
Dim myCell As Range
Dim myRow As Range
Dim myRng As Range
Dim myRowStr As String
Dim myStr As String

Set MyDataObj = New DataObject

Set myRng = Selection.Areas(1)

myStr = ""
For Each myRow In myRng.Rows
myRowStr = ""
For Each myCell In myRow.Cells
myRowStr = myRowStr & vbTab & myCell.Text
Next myCell
myRowStr = Mid(myRowStr, Len(vbTab) + 1) 'get rid of leading vbtab
myStr = myStr & vbCrLf & myRowStr
Next myRow
myStr = Mid(myStr, Len(vbCrLf) + 1) 'get rid of leading vbcrlf (2 chars!)

MyDataObj.SetText myStr
MyDataObj.PutInClipboard

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)