You could use a macro:

Option Explicit
Sub testme()

Dim MyDataObj As DataObject
Dim myStr As String

Set MyDataObj = New DataObject

myStr = activecell.text '.value???

'I'm confused about what clean means.
'vbcr = chr(13); vblf = chr(10); vbcrlf = chr(13)&chr(10)

myStr = Replace(myStr, vbCrLf, " ")
myStr = Replace(myStr, vbCr, " ")
myStr = Replace(myStr, vbLf, " ")

MyDataObj.SetText myStr
MyDataObj.PutInClipboard

End Sub

Chip Pearson has some notes that you'll want to read.
http://www.cpearson.com/excel/clipboard.htm

Especially the note about using tools|references and checking "Microsoft Forms
2.0 object library").

ymgagnon wrote:

I have used the CONCATENATE function to put together a header for a SQL
script all in one cell. In Excel, this uses CHAR(10) as the newline
character, and displays on multiple lines within a single cell. I would like
to be able to copy this to Notepad so I can make a "clean" version to send
along to DBAs, but when it moves to Notepad, I get a big mess of non-printing
characters. (And if I use the CLEAN function in Excel, the format goes away
entirely)

Any ideas how to preserve the format in the cell, but still have the text
copyable to Notepad?

--

Dave Peterson