How can I copy plain text from Excel to Notepad?

[ymgagnon]

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?

 

[JP]

When you select the cell, press Ctrl-C to copy, then switch to Notepad
and paste, is that what happens?

--JP

 

[ymgagnon]

Yes. And it does seem to be an issue with the newline character CHAR(10)
(ASCII 10). But I tried with CHAR(13) instead to see if it would be handled
better...and no luck. I'd like to avoid washing it through too many
steps...but I got it to work if I drop it in Word, then save as a text file.
I'd like it to be as easy to hand over to someone else as possible, though,
and don't relish trying to explain to everyone else who uses it how to save
Word docs as text in the correct format...

 

[JP]

Can you post the formula?

--JP

Yes.  And it does seem to be an issue with the newline character CHAR(10)
(ASCII 10).  But I tried with CHAR(13) instead to see if it would be handled
better...and no luck.  I'd like to avoid washing it through too many
steps...but I got it to work if I drop it in Word, then save as a text file.  
I'd like it to be as easy to hand over to someone else as possible, though,
and don't relish trying to explain to everyone else who uses it how to save
Word docs as text in the correct format...

 

[Dave Peterson]

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]

That might be a bit complicated. It's pulling data from across two tabs,
combining static text with entered values to creat individual lines of the
header, and then the header itself is put together by that final concatenate.
So...the final formula looks like ('Product Lookup - bin' being the second
tab where the lines are being assembled):

=CONCATENATE('Product Lookup - bin'!A4,'Product Lookup - bin'!A5,'Product
Lookup - bin'!A6,'Product Lookup - bin'!A7,'Product Lookup - bin'!A8,'Product
Lookup - bin'!A9,'Product Lookup - bin'!A10,'Product Lookup -
bin'!A11,'Product Lookup - bin'!A12,'Product Lookup - bin'!A13,'Product
Lookup - bin'!A14,'Product Lookup - bin'!A15,'Product Lookup -
bin'!A16,'Product Lookup - bin'!A17,'Product Lookup - bin'!A18,'Product
Lookup - bin'!A19,'Product Lookup - bin'!A20,'Product Lookup -
bin'!A21,'Product Lookup - bin'!A22,'Product Lookup - bin'!A23,'Product
Lookup - bin'!A24,)

Here are a couple from the other tab:
A4: ="/*File Name: "&'Product Lookup'!B4
A5: =CHAR(10)&"|"
A6: =CHAR(10)&"| Script Description: "&MID('Product Lookup'!B6,1,28)

(And so on...)